One record, multiple updates in one query (MySQL) I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can I do it in a single query? This query below works, just that it only works for the first match(?!).

UPDATE test AS t1
LEFT JOIN test AS t2 ON t1.target = t2.obj_id
SET t2.amount = t2.amount + t1.amount, t1.amount = 0
WHERE t1.amount &gt; 0 AND strcmp(t1.target, "null") &lt;&gt; 0;
I might be missing something here, but maybe it only works for the first match because it sets t1.amount = 0, which means that record is excluded from a repeated invocation of the same command (whose WHERE clause requires t1.amount &gt; 0)?