I want to check the duplicates for the combination of A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
Table XYZ is having STATUS column
I want to get the ref_nbr's to update the STATUS Coulmn of XYZ to DEL for the combination of above mentioned duplicates
If I do update with Ref_nbr I should get only 6 records should update
How I can achieve this?
I have written SQL as
Code:
SELECT A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
FROM abc A,
xyz B,
jkl C
WHERE A.REF_NBR = B.REF_NBR
AND B.REF_NBR = B.INWD_REF_NBR
AND C.REF_NBR = A.REF_NBR
AND (B.SO_CMPL_DATE >= '01-OCT-2007'
AND B.SO_CMPL_DATE <= '30-NOV-2007')
AND B.STATUS_CD <> 'DEL'
AND B.SPECIAL_PROCESS_IND IN ('UPR1 ','UPR2 ','UPR3 ')
GROUP BY A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
HAVING COUNT(*) > 1
but from the above I am not able to get the Ref_nbrs to update the XYZ table.
Try out these query. I did not test this one and also I have made an assumption that ref_nbr is unique from ur data.
Update ABC A set A.status = 'D'
where A.ref_nbr in( select B.ref_nmbr from XYZ B
Where exists ( select 1 from XYZ C
where b.PROD = C.PROD
and b.USOC = C.USOC
and b.ORDER_NBR = C.ORDER_NBR
and b.DATE = C.DATE
and B.ref_nbr <> C.ref_nbr ));
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
Here is the query which I used for the problem
Code:
UPDATE XYZ
SET STATUS = 'DEL'
WHERE REF_NBR IN
(SELECT REF_NBR FROM (
SELECT B.REF_NBR, B.STATUS,
COUNT(B.STATUS) OVER (PARTITION BY A.PROD,A.USOC_CD,A.TN,B.ORDER_NBR,B.DATE) CNT
FROM XYZ B
,ABC A
,JKL C
WHERE B.REF_NBR = A.REF_NBR
AND B.REF_NBR = B.REF_NBR1
AND C.REF_NBR = A.REF_NBR
AND (B.DATE >= '01-OCT-2007'
AND B.DATE <= '31-DEC-2007')
AND B.STATUS <> 'DEL'
AND B.SP_IND IN ('UPR1 ','UPR2 ','UPR3 '))
WHERE CNT > 1)
