When you are performing a SQL UPDATE on multiple rows in MySQL, it seems that you can either put single quotes around every value or not and it will produce the same results.

Is there a reason for using single quotes in an UPDATE query if not using single quotes produces the same results? Is there any difference at all?

Example without quotes:

UPDATE superuser
SET poop = rainbows
WHERE id IN (1, 2, 3);

Example with quotes:

UPDATE superuser
SET poop = rainbows
WHERE id IN ('1', '2', '3');
  • Are you asking what the difference between the two queries are? – Ramhound Oct 19 '16 at 12:53
  • @Ramhound - Yes, I am asking if there is any difference. Thanks. – THE DOCTOR Oct 19 '16 at 13:57
  • I assume you have ran each query, and return what the results would have been, before asking this question? Just switch the UPDATE to SELECT and get rid of the SET. – Ramhound Oct 19 '16 at 14:38

In general, quote all strings and unquote numbers. Your SQL will, however, break if there are single quotes on the string, so escape the single quote by doubling it.

SELECT * FROM superuser WHERE poop = 'johnny''s'
-- note how the string [johnny's] have double quotes

On the other hand, quoting numbers in SQL is bad. Although SQL implicitly tries to convert these values, it could lead to unwanted results. Say, you have a CHAR type and you feed it an unquoted 1 instead of a quoted '1', you'd get the character in the 1st position of the charset instead of an actual '1' character.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.