mysql update math

Question

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

Answer 1

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

This is similar to http://stackoverflow.com/questions/238800/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.

Answer 2

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

Answer 3

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

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

don't know why though!