Nothing can be compared to null, not even null, which implies that MySQL is wrong in returning two rows in the SELECT. Your query needs to say IS NULL:
delete from t where id = 1 and num is null; Arthur -----Original Message----- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 12:26 PM To: [EMAIL PROTECTED] Cc: '[EMAIL PROTECTED]' Subject: RE: delete where column <=> null David, I get the same behaviour with mysql 3.23.47 (not max) and with mysql v 4.0.4-beta Without the index it works fine. Must be a bug.... Cheers, Andrew -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 16:45 To: [EMAIL PROTECTED] Subject: delete where column <=> null Hi, I'm trying to delete from a table where col <=> null, but it doesn't seem to work. I don't see anything in the manual that says that <=> can't be used in the where clause of a delete statment, but: mysql> select * from t; +----+------+ | id | num | +----+------+ | 1 | NULL | | 1 | NULL | +----+------+ 4 rows in set (0.00 sec) mysql> delete from t where id = 1 and num <=> null; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1 and num <=> null; +----+------+ | id | num | +----+------+ | 1 | NULL | | 1 | NULL | +----+------+ 2 rows in set (0.00 sec) This behavior seems very bizarre and counter-intuitive to me, but it's such an obvious bug that I'm afraid it must be a feature and not a bug. Can anyone help/explain? Using "is null" in the where clause works, of course, but would be relatively cumbersome to use. Db is 3.23.53-max-nt Thanks, -David p.s. in case it's relevant, there is a unique index on (id,num): create table t ( id integer not null, num integer, unique index id_num_idx(id,num) ); --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php