Posted: Fri Oct 15, 2010 3:40 pm Post subject: DELETE multiple records from a table
Hi,
I was trying to delete some records from a table, but not able to do it.. Could anyone please suggest the best way to delete the records from DB2 table as per the below scenario..
I have a table called table1 having following rows...
If we will exclude col4 from this table then we can see there are duplicate records in this table. From this duplicate records, only the latest record needs to be delted.
Here the following lines will be considered as the duplicates and needs to be removed..
Hi Papadi -
If you will exclude col4 from the table then you can see there are 3 set of duplicate records. The latest record ( based on the col4's sequence ) needs to be deleted..
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Fri Oct 15, 2010 4:33 pm Post subject:
batu544,
Unless I am missing something , isn't it a simple case of finding the max of col4 and deleting it ?
ex : untested sql
Code:
DELETE
FROM table
WHERE (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9) IN
(SELECT COL1,COL2,COL3
,MAX(COL4)
,COL5,COL6,COL7,COL8,COL9
FROM table
GROUP BY COL1
,COL2
,COL3
,COL5
,COL6
,COL7
,COL8
,COL9);
Hi Kolusu,
I am sorry for not including a not duplicate condition in my example.. I can't use the MAX of col4 always.. because there could be a rows for which no duplicates available.. if I will use directly the max function then it will delete the non-duplicate record also..
Also, I fear to put all the columns in where IN condition because in my table there are 54 columns and when I am putting all the columns in 'IN' condition its really taking much time and much relative cost in my test region. So, it will definitely take much more in production region..
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Fri Oct 15, 2010 5:34 pm Post subject:
Quote:
I am sorry for not including a not duplicate condition in my example.. I can't use the MAX of col4 always.. because there could be a rows for which no duplicates available.. if I will use directly the max function then it will delete the non-duplicate record also..
batu544,
You can avoid the deletion of non duplicate rows by adding another condition on the inner sql by like shown below
Code:
GROUP BY COL1
,COL2
,COL3
,COL5
,COL6
,COL7
,COL8
,COL9 HAVING COUNT(*) > 1 );
This will skip the deletion of all the unique rows.
Quote:
Also, I fear to put all the columns in where IN condition because in my table there are 54 columns and when I am putting all the columns in 'IN' condition its really taking much time and much relative cost in my test region. So, it will definitely take much more in production region..
You want to compare the entire 54 columns for duplicates? It is better to unload the table and run it thru a sort and delete the max (col4) records and reload it back
Joined: 02 Dec 2002 Posts: 700 Topics: 63 Location: USA
Posted: Mon Oct 18, 2010 12:42 pm Post subject:
Quote:
Primary key is the combination of col1,col2,col3 and col4.
If col5 thru col54 is same for a given combination of col1 thru col3, as appears in your sample, then you may skip col5 thru col54 in kolusu's query. _________________ Regards,
Diba
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
It is better to unload the table and run it thru a sort and delete the max (col4) records and reload it back