I have written SQL to find the duplicates for the combination of
prod,usoc,tn,order_nbr and date as below
FROM abc A,
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
After this I need to update the STATUS code for those Duplicate records to DEL by using the REF_NBR match
I am not able to retrieve the REF_NBR from the above query which is unique
If you tried to execute the exact query as posted above, you should really consider reading manual about use of the SELECT statement, as well as GROUP BY and HAVING clauses. Once you make the SELECT work, move on to UPDATE.