0

I want to delete some duplicate rows (ID is duplicate), based on its row number (there is no primary key), so that I can thereafter make the ID column my primary key.

My current query does give back the correct rows I want to delete:

SELECT *
FROM table_name
WHERE @row_number:=@row_number NOT IN (
    SELECT * FROM (
        SELECT MIN(@row_number:=@row_number) 
        FROM table_name
        GROUP BY id
    ) x
);

But when I try to delete them using the following query, nothing happens.

DELETE
FROM table_name
WHERE @row_number:=@row_number NOT IN (
    SELECT * FROM (
        SELECT MIN(@row_number:=@row_number) 
        FROM table_name
        GROUP BY id
    ) x
);

Any idea what I'm doing wrong here? Help is highly appreciated!

Dendrobates
  • 3,294
  • 6
  • 37
  • 56

1 Answers1

0

You can use this for setting primary key and remove all duplicated ids:

ALTER IGNORE TABLE `table_name` ADD PRIMARY KEY (`id`);

Solution two:

CREATE TABLE new_table_name AS
SELECT * FROM old_table_name GROUP BY id;
vpalade
  • 1,427
  • 1
  • 16
  • 20