Mysql how two update parent child table in one sql and parent use child’s update column

I have two table. parent and child.

parent has column arrange_quantity, child has a column arrange_quantity too.

parent.arrange_quantity = SUM(child.arrange_quantity).

I want update the two arrange_quantity in one sql. but subquery seems always get old child.arrange_quantity value, how can I get new child.arrange_quanity.

My sql now below :

UPDATE child child 
    LEFT JOIN parent parent ON child.`arrange_id`=parent.`id`
SET child.`arrange_quantity`=11, parent.`arrange_quantity`=(
    SELECT SUM(tmp.arrange_quantity) FROM (SELECT arrange_quantity FROM child WHERE `arrange_id`='PD1509060000225') AS tmp
    )
WHERE 1=1
AND child.`id`=3

after this sql , parent.arrange_quantity equal to 11, now it’s right. but when I want to change child.arrange_quantity to 10,

UPDATE child child 
    LEFT JOIN parent parent ON child.`arrange_id`=parent.`id`
SET child.`arrange_quantity`=10, parent.`arrange_quantity`=(
    SELECT SUM(tmp.arrange_quantity) FROM (SELECT arrange_quantity FROM child WHERE `arrange_id`='PD1509060000225') AS tmp
    )
WHERE 1=1
AND child.`id`=3

parent.arrange still equals to 11, not change to 10. what should I do?


Source: mysql

Leave a Reply