CODEWITHSUNDEEP
mysql
Matthew Rapati
Matthew Rapati

Reputation: 5686

mysql update math

say p.products_price equals 1

why does:

UPDATE products p
SET p.products_price = (1 + p.products_price)
WHERE p.products_id = 8

make p.products_price equals 3?

It is adding 1 to the price and then doing it all over again? I am trying to do something a little more complicated but when it didn't work I broke it down to the simplest thing ever. Can I make some kind of temporary value here and calculate the new price and then set it to that?

Please help I am raging, Thanks.

MySQL client version: 4.1.22

edit: the column is decimal type, i tried the same update on an int column with the same result.

edit: this is not running in code so there is no chance of the code calling the same update twice

Upvotes: 5

Views: 11868

Answers (3)

Matthew Rapati
Matthew Rapati

Reputation: 5686

UPDATE products    
SET products_price = (1 + products_price)    
WHERE products_id = 8

works like it should (removed table alias 'p')

Upvotes: 5

Greg
Greg

Reputation: 321688

That should definitely set products_price to 2. There must be something else going on.

This is similar to Problem with updating a MySQL field with PHP but there was no good solution there so I'll leave this one open too.

Are you running it from the client, or through a script?

Do you have any transactions open or other scripts accessing the database?

Edit: You mentioned joins in your comment - I'd be willing to bet that your join is pulling back the same row more than once.

Upvotes: 0

Will Bickford
Will Bickford

Reputation: 5386

Your SQL looks fine. Is the 'something' column unique? Make sure that you are only updating a single record.

Upvotes: 0

Related Questions