Is there a way to optimize this update query?

Posted by SchlaWiener on Stack Overflow See other posts from Stack Overflow or by SchlaWiener
Published on 2010-06-17T11:16:48Z Indexed on 2010/06/17 11:23 UTC
Read the original article Hit count: 290

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql