devxlogo

Deleting Duplicate Rows

Deleting Duplicate Rows

Question:
I have this table:

 declare tbl1 (x int,y int,z int);

I don’t have a column I could use as a unique key. There are some duplicate rows (not just columns); for example, the row with the values 2,4,7 may occur several times. Is there a simple query I can run to delete all but one duplicate rows?

Answer:
I would revisit the database design to justify why there is a table with no primary key. What sense does it make to store information about an instance of an entity if you can’t tell what instance you mean?

A table without a primary key is not a relation, so don’t expect it to work too well in a relational database.

Unfortunately, the way out of your dilemma is not pretty. You have to use the same table in as many self-joins as you have columns (three in your example) in order to weed out the duplications in each row.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist