Eliminating duplicate rows

[skplipa]

I can't remember the single sql statement to delete the duplicate row from a table.

Do you have any idea.

Suppose a tabel has the following rows.
id name
1 A
2 B
3 C
1 A
2 B
3 C
1 A
2 B
3 C
----
I want to get rid of the duplicate rows. Only thing I remember that a self join including a subquery will give the result but not getting into the exact SQL. Any help?

[Strike]

Well, when you design your tables you should set up primary keys so that duplicate sets of data that can't/shouldn't exist in the DB are not allowed to be set in the DB. So if you want a list of employees, and none of them can have the same first name, last name, and middle initial, you'd set each of those as primary keys. That way you can still have several "John"s and several "Smith"s in the table, but you can't have several "John A Smith"s because that'd violate the key constraints.

[skplipa]

This situation comes when you are trying to sortout some old data or copying data into SQL servers.

[jemfinch]

Originally posted by skplipa
This situation comes when you are trying to sortout some old data or copying data into SQL servers.

Output it to a file and re-input it into a unique field.

Jeremy

[stuka]

Or, in more SQL-ish fashion, create a table for the updates, then populate it from the existing one using the UNIQUE (IIRC) qualifier in your select that feeds the insert.

[BsCis99]

BsCis99: At least in Visual FoxPro's implementation of SQL you simply add the DISTINCT keyword to the SELECT statement.

SELECT DISTINCT * FROM <mytable> INTO TABLE <mydistincttable>

That command will retrieve every field of every row from the table and then do a by-row, field-by-field comparison. For each set of completely identical records, it will retain only 1, thereby ensuring that each row is DISTINCT.

[gish]

<quote>
SELECT DISTINCT * FROM <mytable> INTO TABLE <mydistincttable>
</quote>

that should work, if the id field allows for duplicate entries, which after looking at your sample data, it does.

:sick:

[stuka]

Damn...DISTINCT, not UNIQUE...I knew it was in there somewhere!