How to delete duplicate records in MySQL 3.22?
Hello, Linux folkz: Question: Suppose I have a table: Col1 Col2 --------- A A1 A NULL B B1 B NULL C NULL --------- I would like to delete records with duplicate values in Col1 with NULL in Col2 (C, NULL) has to remain. In M$SQL this is done with a query: DELETE FROM table WHERE Col1 IN (SELECT Col1 FROM table GROUP BY Col1 HAVING count(*)>1); MySQL 3.22 does not support sub-queries. Is there a way to do this job without using sub-queries? Thanks. Alex.
what if you did DELETE tablename WHERE col1 NOT IN (SELECT DISTINCT col1 FROM tablename WHERE col2 = 'NULL') I'm not sure if that would work for you, but would work in Oracle Quite possibly I'm mis-understanding you hth rob (testing my new account... ;-) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Alex Daniloff wrote:
Hello, Linux folkz:
Question: Suppose I have a table:
Col1 Col2 --------- A A1 A NULL B B1 B NULL C NULL ---------
I would like to delete records with duplicate values in Col1 with NULL in Col2 (C, NULL) has to remain.
In M$SQL this is done with a query:
DELETE FROM table WHERE Col1 IN (SELECT Col1 FROM table GROUP BY Col1 HAVING count(*)>1);
MySQL 3.22 does not support sub-queries. Is there a way to do this job without using sub-queries?
participants (2)
-
Alex Daniloff
-
rob