The deletion of duplicate rows cannot be done in a single SQL query like it can be done in Oracle. Hence it needs to be done in 3 steps:
- Create temporary table with one instance of each distinct row
- Delete all rows having multiple instances
- Move back the unique data from temporary table to actual table
Create temporary table with one instance of each distinct row with the below query.
select distinct t.* into temp_table_name from data_table_name t group by keycol1, keycol2, keycol3 having count(0) > 1Note: that this step creates the temp_table_name table so it will fail if a table by that name already exists.
This step puts one instance of each distinct row into the temporary table. However, some of the rows may contain identical values in the key columns but different data in the remaining columns. At this point you need to check the data in the temp_table_name and reconcile which of the rows you wish to keep for a given duplicate key value. You may even need to do these three steps on the temporary table to achieve a set of unique records in the temporary table.
Delete all rows having multiple instances with the below query.
delete data_table_name from temp_table_name where data_table_name.keycol1 = temp_table_name.keycol1 and data_table_name.keycol2 = temp_table_name.keycol2 and data_table_name.keycol3 = temp_table_name.keycol3
Move back the unique data from temporary table to actual table with the below query.
insert into data_table_name select * from temp_table_name
Remember to change the table & column names in the above queries.
Replace data_table_name with your actual table name,
temp_table_name with your choice of name for temporary table and
keycol1..n with the actual table column names!
Check out my previous post here for doing this same thing in Oracle.