0

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!

Improve this question
1
  • 3
    v3 mysql? it's LONG past time you upgraded that dinosaur. Commented Jun 24, 2013 at 16:11

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I see where are you going with this however, I get no results changed when i INNER JOIN Sales b ON a.uid = b.uid. Am I missing something or am I doing the ON statement wrong?
As uid is unique doing that would just double every count and amount. Do you want to do this for all rows, or just uid 1 and uid 2? If between many rows how do you determine which rows values are used to update which other rows values?
Just uid 1 and 2. I'm updating the correct rows with values that were placed in the incorrect rows. I have to manually spot that by eye, unfortunately.
From what I am seeing, the ability to cross update via join is for mysql 4.0 or higher. Thanks for the suggestion though as I know this works in mysql 4.0 and above!
No problem. It doesn't surprise me that it only works on newer versions but unfortunately I had no way to test it.
1

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.

Improve this answer

6 Comments

Nice! This one worked. Thanks! And I know we use such an old version of mysql. But it's not too easy to update 10,000 databases to newer versions of mysql as some functions may no longer work :P
We all know what working with legacy hardware/software means ;)
Actually, I tested this on the wrong version of mysql. This doesn't actually work on our dinosaur mysql version..But still giving you credit for the good answer that would work in newer mysql versions
@WalkerBoh :D Could you provide the error message and your exact version of MySQL -- according to the doc, that should have worked with 3.23. Is it really older that that !?!
It's mysql 3.23.58. Here's what I was using on one of our tables: mysql> update inventory_closing_balance AS T1, inventory_closing_balance AS T2 SET T1.amount = T1.amount + T2.amount where T1.inventory_closing_balance_uid = 244 AND T2.inventory_closing_balance_uid = 243; ERROR 1064: You have an error in your SQL syntax near 'AS T1, inventory_closing_balance AS T2 SET T1.amount = T1.amount + T2.amount whe' at line 1
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.