View previous topic :: View next topic
|
Author |
Message |
annanagarlt
New User
Joined: 31 Oct 2003 Posts: 5
|
|
|
|
Dear All,
I would like to DELETE DUPLICATE Records from a DB2 TABLE
Note : Table doesnt have a Primary Key Definition.
I dont want to use a new table
Pls I beg to explain the QUERY in a more Proper way...
Looking forward in exploring this further...
Cheers
Team AnnaNagar LT |
|
Back to top |
|
|
anuradha
Active User
Joined: 06 Jan 2004 Posts: 247 Location: Hyderabad
|
|
|
|
Hi,
I wonder if I clarify your doubt.your question to me is still not clear .But I am trying to answer.To delete the duplicate rows first you need to find out them.suppose your table say samp contains 3 columns A,B,C then u can find the duplicates by the following query
select A, B, C, count(*) from samp
group by A,B,C
having count(*) > 1
Then after finding this I think you can delete them easily.
OR
IF YOU CAN CREATE A TEMP TABLE U CAN FOLLOW THIS WAY:
CREATE TEMPORARY TABLE samp_temp AS SELECT DISTINCT * FROM samp
Then say:
delete from samp
so now samp is empty
now load all rows(which dont have any duplicates) into the samp table.
THANKS AND REGARDS
ANURADHA |
|
Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005 Posts: 677 Location: NJ
|
|
|
|
hi frnd,
try to post DB2 query in the DB2 section. and according to ur query without the help of another table how can u store the data??? Actually whats ur requirement?? So any way what anuratha said is clear!! but kindly post ur req clearly so that all can understand.. |
|
Back to top |
|
|
Hames
New User
Joined: 03 Oct 2005 Posts: 49
|
|
|
|
Hi,
In Oracle it is like
"delete from test a
where rowid <> ( select max(rowid)
from test b
where a.sno = b.sno
and a.sname = b.sname )".
In oracle using rowid column we can delete the duplicates easily.
But in DB2 rowid column is not available. Anuradha's answer is good for less number of variables. If a table is having more than 50 columns then think about her Query It will be difficult. There will be some simpler way for this.
I will let u know the alternative soon. |
|
Back to top |
|
|
Sandy-is-here
New User
Joined: 16 Mar 2006 Posts: 6 Location: Bangalore
|
|
|
|
Hi All,
Suppose you want to delete the duplicate records from table and keep only one record.
Let me give an example
Table Name : Employee
Empno Name Salary
1 Ramesh 10,000
2 Ramesh 10,000
3 Ramesh 10,000
4 Suneel 20,000
5 James 25,000
6 Pradhan 30,000
7 Nagaraj 12,000
Apart from the above table , need to be delete the duplicate records namely Ramesh and should come the output like below is,
OUT PUT :
Empno Name Salary
1 Ramesh 10,000
4 Suneel 20,000
5 James 25,000
6 Pradhan 30,000
7 Nagaraj 12,000
Answer :
Delete from Employee E1 where empid <> ( select Min( empid) from Employee E2 where E2. empid = E1.empid)
Note :
(1) Having a unique EmpID makes this easy.
(2) By using a self referencing sub-query and the MIN aggregate function, you can determine the lowest ID for a given NAME.
(3) By deleting all but the lowest ID, you are left with a unique set of NAMEs.
Thanks
Sandy |
|
Back to top |
|
|
prakashmuthaiah
New User
Joined: 14 Mar 2006 Posts: 1
|
|
|
|
delete from employee where Name in (select Name from employee group by name having count(*) > 1) |
|
Back to top |
|
|
skkp2006
New User
Joined: 14 Jul 2006 Posts: 93 Location: Chennai,India
|
|
|
|
How about unloading the table into a sequential file....sorting the file with the whole record length as the key and loading the data back to the table ?????
regards,
Syam |
|
Back to top |
|
|
Pons
New User
Joined: 25 May 2007 Posts: 61 Location: Coimbatore
|
|
|
|
Hi prakashmuthaiah,
I think your query will delete not only the duplicate rows; it will delete including the original row also.
Ex: Ramesh 10,000 will totally delete form the table.
So there is no record for Ramesh. But that is not an requirement.
DELETE FROM EMPLOYEE A
WHERE 1 < (SELECT COUNT (NAME) FROM
EMPLOYEE B
WHERE A.NAME = B.NAME);
I think this may help you. If no primay key you merge all the field and use it. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Hi annanagarlt,
You can run a spufi in batch by giving
select distinct(*) for your table have the output in sysrec and the LOAD control card in syspunch
Then having that sysrec and syspunch you can run a LOAD REPLACE for the same table.
You need to modify your LOAD control card before submitting the job.
Hope this helps
Thanks,
Prem |
|
Back to top |
|
|
|