.png)
In this article we will see how to eliminate duplicate rows in SQL Server, in MySQL and Oracle.
How to delete duplicate rows in SQL Server
Suponiendo que no tienes valores nulos, agrupa las columnas exclusivas (por ejemplo, col_1, col_2, col_3) y seleccionas la columna identificadora (el ID) el MIN o MAX (por ejemplo, row_id) como la fila que se debe conservar. Luego, elimine todo lo que no tenía un ID:
DELETE my_table FROM my_table LEFT OUTER JOIN ( SELECT MIN(row_id) as row_id, col_1, col_2, col_3 FROM my_table GROUP BY col_1, col_2, col_3 ) as keep_rows ON my_table.row_id = keep_rows.row_id WHERE keep_rows.row_id IS NULL
If you have a GUID instead of an integer as an ID, use the following.
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), my_guid_column)))
How to delete duplicate rows in MySQL?
Continuing with the previous example, the primary key: id, the unique columns: col_1, col_2, col_3. You can use a temporary table, like:
create temporary table temp_table (id int); insert temp_table (id) select id from your_table t1 where exists ( select * from your_table t2 where t2.col_1 = t1.col_1 and t2.col_2 = t1.col_2 and t2.col_3 = t1.col_3 and t2.id > t1.id ); delete from your_table where id in (select id from temp_table);
Or you can add a UNIQUE index to the table. When you execute this, all duplicate rows will be deleted. As an additional benefit, INSERT futures that are duplicates will fail. And you'd better make a backup before executing this statement.
ALTER IGNORE TABLE your_table ADD UNIQUE INDEX idx_name (col_1, col_2, col_3);
How to delete duplicate rows in Oracle?
The following example deletes all duplicate rows and leaves only one of them in Oracle
DELETE FROM my_table WHERE rowid not in (SELECT MIN(rowid) FROM my_table GROUP BY column1, column2, column3...) ;