Search code examples
sqlapostrophe

Apostrophes in SQL selecting the whole table

I had this weird bug in my company's code from an SQL sentence, that was basically updating all the rows from a table when what we wanted was just to update a single one. The code was the following.

UPDATE table SET 'disconnections'=value WHERE 'connections'=(SELECT MAX('connections') FROM table)

Where the apostrophe surrounding the column names where obviously wrong. So this was updating all the rows in the table; we removed them and the sentence worked as expected. What was the deal with that?

Thanks!

Solution

  • The issue is in MAX('connections'). Obviously you want to use `` instead of just single quotas ''. In your case 'connections' is a string constant not a field name. So MAX(constant) = constant. This condition is TRUE for all records in your table since it doesn't depend on a field with name `connections`.

    I guess it is MySQL so if you want to refer a field name you should use `` instead of ''.