Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Nov 3, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Deleting Duplicate Rows

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?

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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date