Mysql update math

Question

say p.products_price is 1

why:

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

make p.products_price equal to 3?

He adds 1 to the price, and then does it again and again? I'm trying to make something a little more complicated, but when it didn’t work, I broke it down to the simplest. Can I make some temporary value here and calculate a new price and then set it to this?

Please help, I'm raging, Thanks.

MySQL Client Version: 4.1.22

edit: the column is decimal, I tried the same update in the int column with the same result.

edit: this does not work in the code, so it is not possible for the code to call the same update twice

Answer 1

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

works as it should (remote table alias "p")

Answer 2

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

This is similar to the Problem with updating a MySQL field with PHP , but there was no good solution, so I will stay open too.

Do you run it from a client or through a script?

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

Edit: you mentioned joining your comment - I would agree to bet that your connection pulls the same line more than once.

Answer 3

Your SQL looks great. Is the "something" column unique? Make sure you update only one record.