Mysql query: reduce value by 1

Question

I want to decrement the value contained in a field (integer or dropdown) by 1. I tried these 3 queries but none of them worked 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 the solutions I found were similar. Any idea why this doesn't work on my end?

Answer 1

Try removing the single quotes from the column name, otherwise it will be treated as the string "my_field-1" or use backticks around the 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'

Answer 2

You don't need any quotes.

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

To understand, this is like the classic sentiment in any language: "I want my_field to be equal to my_field (current value) minus 1” >.
If quoted, it means "I want my_field to be equal to the string:

1. 'my_field-1' (for your first query)
2. 'my_field' - 1 (This doesn't make any sense, at least to me: what is the result of subtracting an integer from a string?)
3. '-1', if your field has the INTEGER symbolic type, the value will be converted to -1.

In some cases (if your field name has spaces or special characters), you can surround the field name with "backticks":

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

Answer 3

The method is of great importance to the actual payment of wages***gasa***give