Apostrophes in SQL selecting the whole table

Question

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!

What database are you using? Are you sure the character is ' and not ` ? — Alex K., Jan 26 '17 at 11:46
In ANSI SQL single quotes are for string literals, e.g. `'some text'`. Use double quotes for identifiers, e.g. `"column-name"`. — jarlh, Jan 26 '17 at 12:10

score 3 · Accepted Answer · answered Jan 26 '17 at 12:22

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 ''.

Apostrophes in SQL selecting the whole table

1 Answers1