Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 = t2.obj_id
SET t2.amount = t2.amount + t1.amount, t1.amount = 0
WHERE t1.amount > 0 AND strcmp(, "null") <> 0;
share|improve this question
Please add some sample data and the expected result after the update. – Leigh Riffel Jan 14 '11 at 19:34

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 > 0)?

share|improve this answer
If I have 2 records that have the same target, when I reset the value of one of these(t1.amount = 0), it should not influence the second record, so when the second gets added to the target record, the target should have the sum of the two records. I just get the first record's value in the target, the rest are set to 0. – user412 Jan 13 '11 at 18:48

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.