7

I would like decrease by 1 the value contained inside a field (integer or drop-down). I tried these 3 queries but none of them work as expected:

UPDATE `my_table` SET `my_field` = 'my_field-1' WHERE `other` = '123'

UPDATE `my_table` SET `my_field` = 'my_field' -1 WHERE `other` = '123'

UPDATE `my_table` SET `my_field` = '-1' WHERE `other` = '123'

I searched here and on Google but all solutions I found are similar. Any idea why this doesn't work at my side?

elixenide
  • 44,308
  • 16
  • 74
  • 100
dotcom22
  • 249
  • 1
  • 7
  • 19
  • If you didn't put back ticks around all your column names you might have got it right on that second attempt – OGHaza Feb 16 '14 at 16:14

2 Answers2

12

You don't need any quotes. 

UPDATE my_table SET my_field = my_field - 1 WHERE `other` = '123'

To understand, it's like a classic affectation in any languages: "I want my_field being equal to my_field (the current value) minus 1.
If you put quotes, it means "I want my_field being equal to the string:

  1. 'my_field-1' (for your first query)
  2. 'my_field' - 1 (which means nothing, at least for me: what the result of a string minus an integer?)
  3. '-1', which will be converted to -1 if your field has the INTEGER signed type.

In some cases (if you have spaces or special characters if your field name), you can surrounded the field name with `backticks`:

UPDATE my_table SET `my_field` = `my_field` - 1 WHERE  other = '123'
Maxime Lorant
  • 34,607
  • 19
  • 87
  • 97
7

Try this one remove single quotes from the column name other it will be treated as string 'my_field-1' or use back-ticks around column name

UPDATE my_table SET my_field = my_field - 1 WHERE `other` = '123'

or 

UPDATE my_table SET `my_field` = `my_field` - 1 WHERE  `other` = '123'
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118