October 6, 2011 at 1:55 am
I have a table named "computers_deviceprocessors" which contains 'id' as the PRIMARY KEY; 'computer_id' FOREIGN KEY; 'deviceprocessor_id' FOREIGN key. In my other table name "deviceprocessors", my PRIMARY KEY is 'deviceprocessors_id'; and a field named "NumberDeployed". I have this query below:
SELECT COUNT(deviceprocessors_id)
FROM computers_deviceprocessors
GROUP BY deviceprocessors_id
I want the result of that query to be updated in the table "deviceprocessors" in the field "NumberDeployed" which has the same deviceprocessors_id
October 6, 2011 at 10:59 am
I've assumed this schema:
CREATE TABLE computers_deviceprocessors (id int PRIMARY KEY, computer_id int, deviceprocessor_id int)
CREATE TABLE deviceprocessors(id int PRIMARY KEY, number_deployed int)
...and this sample data:
INSERT INTO computers_deviceprocessors (id, computer_id, deviceprocessor_id)
VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 2, 3), (5, 4, 3)
Now... keep in mind that if the rows don't exist in the table, they won't be updated or inserted... but here's how to do it:
UPDATE deviceProcessors
SET number_deployed = (
SELECT COUNT(deviceprocessor_id)
FROM computers_deviceprocessors
WHERE computers_deviceprocessors.deviceprocessor_id = deviceProcessors.id
GROUP BY deviceprocessor_id
)
The above will have no effect because in my example there are no rows to be updated. However, putting them in is easy... If you want to insert rows into the table, you'd do it like this (will fill in any missing rows):
INSERT INTO deviceprocessors
SELECT deviceprocessor_id, COUNT(deviceprocessor_id)
FROM computers_deviceprocessors cd
WHERE NOT EXISTS (SELECT 1 FROM deviceprocessors d WHERE d.id = cd.deviceprocessor_id)
GROUP BY deviceprocessor_id
So now, if you just put the insert in first (add all new rows) followed by the update (update all existing rows) you'll have what I think you want.
October 6, 2011 at 2:54 pm
Thanks Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply