CODEWITHSUNDEEP
mysqlincrement
Walker Boh
Walker Boh

Reputation: 770

MYSQL - How to increment fields in one row with values from another row

I have a table that we'll call 'Sales' with 4 rows: uid, date, count and amount. I want to increment the count and amount values for one row with the count/amount values from a different row in that table. Example:

UID | Date       | Count | Amount|
1   | 2013-06-20 | 1     | 500   |
2   | 2013-06-24 | 2     | 1000  |

Ideal results would be uid 2's count/amount values being incremented by uid 1's values:

UID | Date       | Count | Amount|
1   | 2013-06-20 | 1     | 500   |
2   | 2013-06-24 | 3     | 1500  |

Please note that my company's database is an older version of MYSQL (3.something) so subqueries are not possible. I am curious to know if this is possible outside of doing an "update sales set count = count + 1" and likewise for the amount columns. I have a lot of rows to update and incrementing the values individually is quite time consuming if you can imagine. Thanks for any help or suggestions!

Upvotes: 0

Views: 680

Answers (2)

Kickstart
Kickstart

Reputation: 21533

Without using a subselect you may be able to do a JOIN. Not sure from your description on what columns you are linking the rows to each other to decide which to update, but the following might give you the idea

UPDATE Sales a
INNER JOIN Sales b
ON ..........
SET a.Count = a.Count + b.Count,
a.Amount = a.Amount + b.Amount

However not sure if this works on archaic versions of MySQL

If you are just updating row 2 based on the values in row 1 then the following should do it

UPDATE Sales a
INNER JOIN Sales b
ON a.uid = 2 AND b.uid = 1
SET a.Count = a.Count + b.Count,
a.Amount = a.Amount + b.Amount

Upvotes: 2

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

Most of the time, subqueries could be rewritten as join ... and even MySQL 3.23 has multiple table UPDATE

Something like that would probably do the trick ... but I am unable to test it (since your the only one still using such an old version of MySQL ;)

UPDATE Sales AS S1, Sales AS S2
    SET S1.`count` = S1.`count̀ +S2.`count`, S1.Amount = S1.Amount + S2.Amount
    WHERE S1.uid = 2 AND S2.uid = 1

For simplicity here I explicitly set S1.uid to "2" and S2.uid to "1" -- if that works for this line, you should be able to use the WHERE clause that correspond to your specific needs.

Upvotes: 1

Related Questions