sql problem,.. doubles of each row

[r6ashih]

hi i imported a table from a backup and it made doubles of each row. how do i delete the double so there is only 1 of each kind and not 2?

 

[GtPrOjEcTX]

could be an easier way but I'd write a procedure to id each row an alternating 1 and 2, then delete all with id 2

 

[Haircut]

You can use a self-join to de-duplicate data.

Assuming you have a unique value in one column in your table (if not you can always temporarily append a column and assign a monotonically increasing sequence to that column) then you can use the following method:

delete from table
where increasing_seq_column in
(select tab1.increasing_seq_column
from table tab1, table tab2
where tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and ... (repeat for all columns you want to filter on)
and tab1.increasing_seq_column > tab2.increasing_seq_column
)

e.g. If I had a table called users that had the following data

ID..........Surname...........Forename
1............Smith.................John
2............Smith.................John
3............Jones.................Jim
4............Jones.................Jim
.
.
.

Then I would use

delete from users
where ID in
(
select usr1.ID
from users usr1, users usr2
where usr1.surname = usr2.surname
and usr1.forename = usr2.forname
and usr1.ID > usr2.ID
)

 

[r6ashih]

thanx for the help!