|
View previous topic :: View next topic
|
| Author |
Message |
Help-Me-Out
New User
.jpg)
Joined: 09 Dec 2006
Posts: 56
Location: Pune
|
|
|
|
Hi,
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 B1
123 ABC C1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC B1
Output should be
CUST_ID NAME DEPT
124 ABC A1
125 ABC A1
Thanks |
|
| Back to top |
|
 |
murmohk1
Senior Member
.jpg)
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
| Back to top |
|
|
padmaambati
New User
Joined: 14 Nov 2007
Posts: 1
Location: hyderabad
|
|
|
|
| i think we can use many operationsbut u had asked only for single query,in my point of view the better one is "select count(distinctcust_id)from tablename.the other querries are delete,join.union,group by |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello Paddu and welcome to the forums,
| Quote: |
| in my point of view the better one is "select count(distinctcust_id)from tablename |
How does this relate to the original request to delete duplicates?
| Quote: |
the other querries are delete,join.union,group by
|
How will we use this information? |
|
| Back to top |
|
|
Help-Me-Out
New User
Joined: 09 Dec 2006
Posts: 56
Location: Pune
|
|
|
|
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
I went thru the previous post but the answer is not what the above output is.. |
|
| Back to top |
|
|
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
Hi Sandy,
Using Distinct verb in your select query you can achive your results. |
|
| Back to top |
|
|
Help-Me-Out
New User
Joined: 09 Dec 2006
Posts: 56
Location: Pune
|
|
|
|
| Can you give me the query... mine is nt working.. |
|
| Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
| Code: |
SELECT DISTINCT CUST_ID FROM TABLE-NAME |
Use the respective coloumn name in place of CUST_ID. |
|
| Back to top |
|
|
arivazhagan_k
New User
Joined: 05 Dec 2007
Posts: 57
Location: chennai
|
|
|
|
ksk ,
what about for this requirement ?
| Code: |
INPUT DATA
CUST_ID NAME DEPT
123 ABC A1
123 ABC A1
123 ABC B1 --> see
123 ABC C1 --> see
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1
OUTPUT
123 ABC A1
123 ABC B1
123 ABC C1
124 ABC A1
125 ABC A1
126 ABC A1 |
|
|
| Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
arivazhagan.k,
Wirte 2 DISTINCT queries for CUST_ID and DEPT and use UNION so that if any duplicates exists, will get eliminated.
For your above data, for CUST_ID, you will get
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
for DEPT,
123 ABC A1
123 ABC B1
123 ABC C1
If you do UNION on these duplicate record 123ABCA1 will get deleted and you will get the unique results.
Please correct me if I am wrong in my solution.
Regards,
KSK |
|
| Back to top |
|
|
arivazhagan_k
New User
Joined: 05 Dec 2007
Posts: 57
Location: chennai
|
|
|
|
How about this query ?
| Code: |
SELECT CUST_ID,NAME,DEPT
FROM CUSTOMER
GROUP BY CUST_ID,NAME,DEPT |
|
|
| Back to top |
|
|
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 156
|
|
|
|
| The easiest way I can think of is, unload the Data to a flat file and run it through a sort job to eliminate the dups and then load it into the table again. It can get very complex in a DB2 query. |
|
| Back to top |
|
|
Help-Me-Out
New User
Joined: 09 Dec 2006
Posts: 56
Location: Pune
|
|
|
|
 sorry, but no one is giving me the reply how to delete the duplicate rows.... everyone is concetrating on the selecting the rows 
i wanna delete the duplicate rows..... 
plz help me |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| i wanna delete the duplicate rows |
before deleting them You must find them...
if this is not homework why insist on "single query"
when sweeping a whole table
from a point of view of performance, space utilization recovery
the BEST thing to do is ..
unload, process, reload
finding out how many duplicates You have is the first thing to do
in order to decide how to carry on any further updates |
|
| Back to top |
|
|
abhishekmdwivedi
New User
.jpg)
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
| 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) ; |
|
| Back to top |
|
|
die7nadal
Active User
Joined: 23 Mar 2005
Posts: 156
|
|
|
|
| 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 |
|
|
mastrahoyar
New User
Joined: 03 Mar 2007
Posts: 17
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: 53
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 |
|
|
|
|
