die7nadal
Active User
Joined: 23 Mar 2005
Posts: 142
|
|
|
|
| abhishekmdwivedi wrote: |
| Quote: |
Hi correcting my previous question..
Can any one has idea about how to delete all duplicate rows from table with single query?
e.g. table contains data like
CUST_ID NAME DEPT
123 ABC A1
123 ABC A1
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1
Output should be
CUST_ID NAME DEPT
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
|
As per the above requirement , below query may do the trick.
DELETE FROM table_name where
CUST_ID in
(SELECT CUST_ID from table_name
GROUP BY CUST_ID HAVING COUNT(*) > 1) ; |
This query will delete all the dups, but the requirement was to keep 1 record among the duplicate records. So ineffect the below records will not be present in the output.
123 ABC A1
126 ABC A1 |
|
