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.
