Hi all! I got it to work! :-) And all I did was replacing "IS NULL" with "= '0000-00-00 00:00:00'" in the DELETE-statement...
Does anyone have an explanation for this...? Regards, Evert Michael Stassen wrote: > Evert wrote: >> Hi all! >> >> Who can tell me why the following does not work...? >> >> When I do: >> SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND >> condition2 IS NULL; >> >> it returns: >> +---------+ >> | counter | >> +---------+ >> | 2 | >> +---------+ >> >> Then I do: >> DELETE FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; >> >> it returns: >> Query OK, 0 rows affected (0.00 sec) >> >> But... when I give a >> SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND >> condition2 IS NULL; >> >> How come the 2 records did not get deleted...? >> >> Regards, >> Evert > > What you have shown us is perfectly valid SQL that should produce the > desired results. Your mysql version (4.1.21) should make no > difference. I see only 2 possibilities: > > 1. There is actually a difference between the two queries (some typo) > that your editing has hidden. I know you said you've only done a search > and replace on the table name (really?), so this is unlikely, but we on > the list can't conclusively rule it out without seeing the unmodified > original queries. Sorry for the rant, but it has frequently been the > case that the problem has turned out to be in the real query but not in > the edited-for-the-list version. > > 2. Something odd is going on. One possibility is that an index used > by one query but not the other is out of sync with the actual data. > Either you have two rows which are not found by the delete, or your > select is counting two nonexistent rows. It would be nice to know which. > > Have you verified that the two rows actually exist by inspecting their > contents rather than simply counting them? That is, what is the output of > > SELECT condition1, condition2 > FROM table1 > WHERE condition1 = 'A' AND condition2 IS NULL; > > Do you get two rows of output? What about > > SELECT * > FROM table1 > WHERE condition1 = 'A' AND condition2 IS NULL; > > Do you get the same two rows (including the other columns)? > > Have you tried CHECK/REPAIR? > <http://dev.mysql.com/doc/refman/4.1/en/check-table.html> > <http://dev.mysql.com/doc/refman/4.1/en/repair-table.html> > > If you still need help, include the output of > > SHOW CREATE TABLE table1; > > Michael > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
