| Delete Duplicate Records from Table [message #25875] |
Thu, 22 May 2003 07:45  |
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 #25882 is a reply to message #25879] |
Thu, 22 May 2003 10:31  |
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 |
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 :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
