Add a cell from two or more rows in the same MySql table

Question

I have x rows of a column value and "simply" want to add them together and update the highest row number and zero out the other values:

SELECT rowId, addCol FROM table WHERE rowId=1 OR rowId=2

rowId | addCol
1         | 100
2         | 200
3         | 67

This is kind of what I want (I know it's completely wrong) but not sure how to express it!

UPDATE table SET addCol3=addCol1+addCol2+addCol3, addCol1=0, addCol2=0

rowId | addCol
1         | 0
2         | 0
3         | 367

Answer 1

UPDATE table t1
JOIN table t2 ON (t1.rowId > t2.rowId)
LEFT OUTER JOIN table t3 ON (t1.rowId < t3.rowId)
SET t1.addCol = t1.addCol + t2.addCol,
    t2.addCol = 0
WHERE t3.rowId IS NULL;

Explanation: any SQL expression that references more than one row should be done with a JOIN. In this case, we want t1 to be the row that has no greater rowId in any other row. If t1 has the greatest rowId, this means t3 would find no matching row, and therefore t3 would be NULL.

Now that we know t1 has the greatest rowId, match that to all other rows (t2) with a lesser rowId. Zero out the t2 value after adding it to the cumulative total in t1.