Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap