View previous topic :: View next topic
|
Author |
Message |
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Hi
Here is the Table Sructure:
Emp-ID Suffix Amt
===== ==== ===
10 1 100
10 2 200
10 3 150
10 4 250
Primary Keys : Emp-ID & Suffix.
Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row.
Please Help me. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
a db2 table does not contain records, just rows
already discussed quite a few times,
search the forums with db2 delete duplicate rows
You will find certainly an answer to Your need |
|
Back to top |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
Hi,
We in our project had the same problem and we did this by looping Cursors in our Cobol program. I am giving you an example of delete cursor below. Please note that you need to skip the delete query first time and then you can delete every subsequent duplicate row.
Also I tried to do this with a single query but could not succed in DB2, though it is possible in different ORACLE databases.
EXEC SQL
DECLARE THISEMP CURSOR FOR
SELECT EMPNO, LASTNAME,
DEPT, JOB
FROM EMP
WHERE DEPT = 'D11'
FOR UPDATE OF JOB
END-EXEC.
EXEC SQL
OPEN THISEMP
END-EXEC.
EXEC SQL
FETCH THISEMP
INTO :EMP-NUM, :NAME2,
:DEPT, :JOB-NAME
END-EXEC.
EXEC SQL
DELETE FROM EMP
WHERE CURRENT OF THISEMP
END-EXEC.
EXEC SQL
CLOSE THISEMP
END-EXEC.
Thanks
N |
|
Back to top |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Thanks Keanehelp
Is it possible to do in SPUFI? |
|
Back to top |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Hi enrico-sorichetti,
"db2 delete duplicate rows" -- I didnt get something useful for my purpose. Can uoy please help me? |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
If you want to use it only for Emp-Id 10. You can use the following
Code: |
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = 10)
and A.Emp_Id = 10; |
If you want to use it for all Emp-Ids you can use the following.
Code: |
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = A.Emp_id); |
Even though I tested it, I suggest you please try with a ROLLBACK and verify the result before you actually use this SQL. |
|
Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005 Posts: 89
|
|
|
|
Srihari Gonugunta wrote: |
If you want to use it only for Emp-Id 10. You can use the following
Code: |
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = 10)
and A.Emp_Id = 10; |
If you want to use it for all Emp-Ids you can use the following.
Code: |
DELETE FROM TAB A
WHERE A.Suffix <>
(SELECT MAX(B.Suffix)
FROM TAB B
WHERE B.Emp_Id = A.Emp_id); |
Even though I tested it, I suggest you please try with a ROLLBACK and verify the result before you actually use this SQL. |
Nobody said primary key is unique.
How it'll work if MAX(Suffix) is repeated for Emp_id ?
The best way known to me:
Quote: |
1. insert this single row in the Global Temporary Table
2. delete from TAB all rows with Emp_Id = 10 (or another)
3. insert into TAB selected row from the Global Temporary Table |
Lenny |
|
Back to top |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
Hi Debasis,
No, you can not execute this in QMF as you can only use cursors in Cobol program.
Thanks
Nitin |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
a mild warning, before proceeding with mass updates/deletions
- find out how many records/rows/segments are involved in the process ( (duplicates in this case)
- depending on the number of records/rows/segment involved choose the method...
if the number of <things> involved is more than 30% (*) it might be worth meditate on an alternative approach ...
unload, offline processing and reload
30% is usually the limit between sequential/random processing, unload/reload
the issue is valid for all environments KSDS/DB2/IMS/DB |
|
Back to top |
|
|
Debasis Misra Warnings : 1 New User
Joined: 16 Sep 2008 Posts: 72 Location: Bangalore
|
|
|
|
Hi enrico,
The no of records will be very less. Maximum 5-6 recods. |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
Thanks for that info enrico. I was always curious as to the break-even point of when to update and when to unload/reload. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Hi Terry,
rule of thumb, the exact figure is not really important might even be lower
the important thing is to realize the need for such evaluation
and proceed being aware of what might be going on
for dbms let db2 or ims/db other factors would be enq and log issues...
a simple sql
delete from some_table where some_condition
might cause abends for enq buffers overrun, delays for log switching, add as many as You want
backout of the delete abend, a real resopurce hog |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
lkhiger wrote: |
Nobody said primary key is unique. |
I thought that was part of the definition |
|
Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005 Posts: 89
|
|
|
|
GuyC wrote: |
lkhiger wrote: |
Nobody said primary key is unique. |
I thought that was part of the definition |
In this case I don't understand what does it means:
Quote: |
Primary Keys : Emp-ID & Suffix.
Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row. |
Lenny |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
well : maybe that the primary key exists of 2 columns : Emp-id , Suffix |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
The TS has simply overinformed us by posting irrelevant info,
giving everybody a chance to add noise to the topic
Quote: |
Requiremnt: Needs to delete all the duplicate records for the Emp-ID = 10, except any one of the row. |
... really this topic was started under the sillyness insignia...
from the columns description there is an amount involved...
so the employee in the next pay slip will be credited or debited a random amount...
and people speak about IT results being determinstic and repeatable |
|
Back to top |
|
|
|