Is there a way to optimize this update query?
- by SchlaWiener
I have a master table called "parent" and a related table called "childs"
Now I run a query against the master table to update some values with the sum from the child table like this.
UPDATE master m SET
quantity1 = (SELECT SUM(quantity1) FROM childs c WHERE c.master_id = m.id),
quantity2 = (SELECT SUM(quantity2) FROM childs c WHERE c.master_id = m.id),
count = (SELECT COUNT(*) FROM childs c WHERE c.master_id = m.id)
WHERE master_id = 666;
Which works as expected but is not a good style because I basically make multiple SELECT querys on the same result. Is there a way to optimize that? (Making a query first and storing the values is not an option.
I tried this:
UPDATE master m SET (quantity1, quantity2, count) = (
SELECT SUM(quantity1), SUM(quantity2), COUNT(*)
FROM childs c WHERE c.master_id = m.id
) WHERE master_id = 666;
but that doesn't work.
