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]
