Write SQL query to delete duplicate rows in a table?

Answer: To delete duplicate rows we need to first define the criteria for considering two rows duplicate of each other.

Let say in a given table Test_table if column_1 and column_2 of two rows are same, then these rows are considered equal.

In Oracle we can use rowid of two rows to find that these rows are different.

Query to delete duplicate rows will be as follows:

DELETE FROM
Test_table a
WHERE
a.rowid >
ANY (
SELECT
b.rowid
FROM
Test_table b
WHERE
a.column_1 = b.column_1
AND
a.column_2 = b.column_2
);

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*