0

I am trying to replace many null date values in a single column using this query:

UPDATE animal SET
  BirthDate = '1900-01-01 06:00:00 AM' 
WHERE BirthDate = '''

MySQL shows this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE animal SET BirthDate = '1900-01-01 06:00:00 AM' WHERE BirthDate = ''' at line 1.

How can this be resolved? I use Toad for MySQL interface editor.

  • 1
    In this script BirthDate on where clause is equals to ' ? Try '' or is null – Krismorte Mar 22 '16 at 13:19
  • What datatype is BirthDate? – Rick James Mar 23 '16 at 5:30
  • The AM on the end is ignored. This means that if you have PM, you will get the wrong time. – Rick James Mar 23 '16 at 5:36
1
  • For starters, your GUI shouldn't matter, but it's normally good to use the CLI - i.e. the mysql client - or at least as a backup in case of results you are having problems interpreting.

  • Second, have you tried IS NULL instead of the = comparison?

  • Third, what's wrong with NULL? Normally, you should use the appropriate datatype - why is NULL a problem? If you don't know the date of birth, there's nothing wrong with it. For example, if you want to perform an AVG calculation, you'll have problems. Your population will appear much older than it really is. NULLs however will be ignored in such a calculation, so no anomalous results (the same goes for all such calculations).

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.