Home » SQL & PL/SQL » Client Tools » Delete Duplicate Records from Table
Delete Duplicate Records from Table [message #25875] Thu, 22 May 2003 07:45 Go to next message
Vairamuthu
Messages: 4
Registered: May 2003
Junior Member
How to delete duplicate records from table without creating a temporary table.
Please note that the table TT has no key fields

Table TT
=========
A B
============
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2

Output
======

A B
===========
1 1
2 2
Re: Delete Duplicate Records from Table [message #25878 is a reply to message #25875] Thu, 22 May 2003 08:10 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
delete from tt
where rowid <> ( select max(rowid)
from tt tt2
where tt2.a = tt.a)
Re: Delete Duplicate Records from Table [message #25879 is a reply to message #25878] Thu, 22 May 2003 08:16 Go to previous messageGo to next message
Vairamuthu
Messages: 4
Registered: May 2003
Junior Member
Thanks a Lot!!
Apart from using RowID is there any other way which can work in SQL Server as well.
Re: Delete Duplicate Records from Table [message #25882 is a reply to message #25879] Thu, 22 May 2003 10:31 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
I am not aware of any other way to do it in a single SQL statement if all values of different rows in the table are equal (if you have a date column and the dates are different, then of course it is possible using the same technique as rowid).

If all the columns are the same, you could set up a temporary table. Insert into it all DISTINCT rows from your problem table. Delete from your problem table all rows that are in your distinct table. Then insert into your problem table all the rows from your distinct table.
Re: Delete Duplicate Records from Table [message #25983 is a reply to message #25875] Thu, 29 May 2003 21:05 Go to previous message
Sud
Messages: 63
Registered: September 2002
Member
Try to update a column with rownum, so that you will be able to differentiate the values and then delete them as you need it. Once you are done, bring them back to their originals.
The UPDATE would look like :

UPDATE mytable SET column1 = column1+rownum;

DELETE FROM mytable WHERE column1 = <your critera>;

Check it out.
Good luck :)
Previous Topic: How to list an additional column in a result set of a minus function
Next Topic: ASKING AGAIN ON Positioned Delete
Goto Forum:
  


Current Time: Tue Aug 04 14:38:05 CDT 2020