Re: Why can't I delete these records?
Hi all! I got it to work! :-) And all I did was replacing IS NULL with = '-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]
Re: Why can't I delete these records?
I'll double-check it on another MySQL server I have here (uses same version of MySQL) and will file a bug report if I indeed can replicate the problem. Thank you all for the swift replies! :-) Regards, Evert Joerg Bruehe wrote: Hi Evert, all! Evert Meulie wrote (re-ordered): 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...? 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 got it to work! :-) And all I did was replacing IS NULL with = '-00-00 00:00:00' in the DELETE-statement... Does anyone have an explanation for this...? I don't. I completely agree with Michael: The queries you showed, both using IS NULL, should work the way you expected (in your original posting). So this either is a bug, or there is some other difference involved. What just comes to my mind: Did you run your statements from the same connection, or from different ones ? I think of settings that control the handling of out-of-range values, like TRADITIONAL mode in 5.0 - could they differ for the SELECT and the DELETE ? If you can make a reproducible test case from this, then please file a bug - WHERE conditions must work the same way in all SQL statements for which they are applicable (SELECT, DELETE, and UPDATE). Regards, Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why can't I delete these records?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi! I tried your tip, but = gives the same result as = when I execute the query... :-/ Regards, Evert Rocco wrote: Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Oops, that was just a typo in my previous reply. My script does use 'IS NULL' and not '= NULL'. I also tried '= NULL'. No luck... Evert Rocco wrote: Hello Evert, the = Operator can not work with NULL values , so you have to use = or IS NULL. I have no clue why its not working at your side, but i assure you that it must be a rather simply problem (typo, table definition). Are you sure your definition2-column allows NULL values in the first place? Greets Rocco Evert wrote: Hi! I tried your tip, but = gives the same result as = when I execute the query... :-/ Regards, Evert Rocco wrote: Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
