|
|
| Author |
Message |
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 154
|
|
|
|
| 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 |
|
| Back to top |
|
 |
References
|
 Posted: Fri Jan 04, 2008 4:51 am Post subject: Re: Re: Reply to: How 2 delete dup rows with single qury in DB2? |
|
|
|
|
mastrahoyar
New User
Joined: 03 Mar 2007
Posts: 7
Location: hyd
|
|
|
|
Hi abhishek.
If table contains data like
CUST_ID NAME DEPT
123 ABC A1
123 EFG 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
123 EFG A1
124 ABC A1
125 ABC A1
126 ABC A1
Now can you please tell me the query which delete duplicate rows.. |
|
| Back to top |
|
|
samuel_Inba
New User
Joined: 03 Jan 2008
Posts: 35
Location: Chennai
|
|
|
|
hi,
I have a doubt pls clarify me,
DELETE FROM table_name where
CUST_ID in
(SELECT CUST_ID from table_name
GROUP BY CUST_ID HAVING COUNT(*) > 1) ;
The inner query will return all the CUST_ID which has rows more than one,
and the outer query will delete all the rows which was returned by the inner query.
but as per the above requirement,
One occurance should be kept... not all of them to be deleted.
e.g:
1
1
1
2
2
then the output shud be
1
2
...
pls let me know if i understood incorrectly. |
|
| Back to top |
|
|
|
|
