30

I'm trying to come up with a MySQL query that will update points... Can I do something like this?

UPDATE `a75ting`.`username` SET `points` = '`points` - 5'
p.campbell
  • 98,673
  • 67
  • 256
  • 322
Shawn
  • 933
  • 4
  • 18
  • 29

2 Answers2

87
UPDATE a75ting.username
SET points = points - 5

by putting the single quotes around the "points -5", you converted that expression into a plaintext string. Leaving it without the quotes lets MySQL see you're referring to a field (points) and subtracting 5 from its current value.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 2
    +1 for cathing the error **and** removing the unnecessary backticks –  Mar 21 '11 at 21:04
  • Note that this will affect zero rows if the value of points is null. Therefore: UPDATE a75ting.username SET points = ifnull(points,0) - 5; – Vincent Jul 05 '18 at 18:29
  • @a_horse_with_no_name backticks are helpful, if you did not give space between the keywords and table columns, the query will be executed. If you omit you should give space. – Boopathi D Nov 25 '21 at 11:39
7

Run this query to find out the difference:

SELECT '`points` - 5' AS string, `points` - 5 AS expression
FROM a75ting.username
Álvaro González
  • 142,137
  • 41
  • 261
  • 360