Mysql – How to replace an empty date value

MySQL

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.

Best Answer

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

Related Solutions

Mysql – What’s wrong with this MySQL query

You are using the wrong quotes. Use ` instead of '

CREATE TABLE `topics` (
  `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `creator` varchar(255) NOT NULL,
  `createtime` datetime NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB;

I also would recommend specifying the engine.

Mysql – Setting a column’s default value by adding a constraint

This is a rather contrived example, but you'll get the idea.

mysql> create table b(a int primary key, c varchar(3), unique key x_b_uk (c));
Query OK, 0 rows affected (0.46 sec)

mysql> alter table b alter c set default 'xyz';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into b (a) values(1);   <<== didn't set field c!
Query OK, 1 row affected (0.39 sec)

mysql> select * from b;
+---+------+
| a | c    |
+---+------+
| 1 | xyz  |                    <<===== value of c = 'xyz'!
+---+------+
1 row in set (0.16 sec)

mysql>

Check here.

Best Answer

Related Solutions

Mysql – What’s wrong with this MySQL query

Mysql – Setting a column’s default value by adding a constraint

Related Question