Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

I'm testing something in Oracle and populated a table with some sample data, but in the process, I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.

How can I delete all duplicate rows and leave only one of them?

1 Answer

0 votes
by (40.7k points)

You can use the rowid pseudocolumn like this:

DELETE FROM your_table

WHERE rowid not in

(SELECT MIN(rowid)

FROM your_table

GROUP BY column1, column2, column3);

Here column1, column2, and column3 make up the identifying key for each record. You can list all your columns.

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
+1 vote
1 answer
asked Jun 26, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer

Browse Categories