1. Registered User
Join Date
May 2006
Posts
20

My input from a table X is
Code:
```ref_nbr       prod  usoc  tn                    order_nbr    date
0004352641 LPIC  LPIC  8187899951517 C62767057 10/29/2007
0004352642 LDPIC LDPIC 8187899951517 C62767057 10/29/2007
0004352634 CID   CNM   8187899951517 C62767057 10/29/2007
0004352635 CID   CNM   8187899951517 C62767057 10/29/2007
0004352636 CCS   ESM   8187899951517 C62767057 10/29/2007
0004352637 CCS   NWL   8187899951517 C62767057 10/29/2007
0004352638 CCS   RAF   8187899951517 C62767057 10/29/2007
0004352639 LPIC  LPIC  8187899951517 C62767057 10/29/2007
0004352640 LDPIC LDPIC 8187899951517 C62767057 10/29/2007
0004352631 LB19B LB19B 8187899951517 C62767057 10/29/2007
0004352632 KSTTU KSTTU 8187899951517 C62767057 10/29/2007
0004352633 PGOHN PGOHN 8187899951517 C62767057 10/29/2007```

I have written SQL to find the duplicates for the combination of
prod,usoc,tn,order_nbr and date as below

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```
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

any help is appreciated

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,111
Something like this?
Code:
```update your_table set
status = 'DEL'
where ref_nbr in (select a.ref_nbr
from abc a, xyz, jkl
where ...
group by ...
having count(*) > 1
);```
However, I'm not sure I understood what "ref_nbr which is unique" means (so query might need tweaking).

3. Registered User
Join Date
May 2006
Posts
20
Let me clear some more points
Table ABC
Code:
```ref_nbr       prod  usoc  tn                    order_nbr    date
0004352641 LPIC  LPIC  8187899951517 C62767057 10/29/2007
0004352642 LDPIC LDPIC 8187899951517 C62767057 10/29/2007
0004352634 CID   CNM   8187899951517 C62767057 10/29/2007
0004352635 CID   CNM   8187899951517 C62767057 10/29/2007
0004352636 CCS   ESM   8187899951517 C62767057 10/29/2007
0004352637 CCS   NWL   8187899951517 C62767057 10/29/2007
0004352638 CCS   RAF   8187899951517 C62767057 10/29/2007
0004352639 LPIC  LPIC  8187899951517 C62767057 10/29/2007
0004352640 LDPIC LDPIC 8187899951517 C62767057 10/29/2007
0004352631 LB19B LB19B 8187899951517 C62767057 10/29/2007
0004352632 KSTTU KSTTU 8187899951517 C62767057 10/29/2007
0004352633 PGOHN PGOHN 8187899951517 C62767057 10/29/2007```

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 this for the combination of above mentioned duplicates

For example shown above
Code:
```prod  usoc          tn     order_nbr     date        count(*)
LDPIC LDPIC 8187899951517 C62767057 10/29/2007              2
CID   CNM   8187899951517 C62767057 10/29/2007             2
LPIC  LPIC  8187899951517 C62767057 10/29/2007             2```
If I do update with Ref_nbr I should get only 6 records should update
How I can achieve this?

4. Registered User
Join Date
May 2006
Posts
20
Originally Posted by Littlefoot
Something like this?
Code:
```update your_table set
status = 'DEL'
where ref_nbr in (select a.ref_nbr
from abc a, xyz, jkl
where ...
group by ...
having count(*) > 1
);```
However, I'm not sure I understood what "ref_nbr which is unique" means (so query might need tweaking).

If I execute the above query I am getting a.ref_nbr is not a GROUP_BY Expression..

5. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,111