Update Query [message #308368] |
Mon, 24 March 2008 02:42  |
shrivatsa
Messages: 3 Registered: March 2008 Location: Pune
|
Junior Member |
|
|
I have a table ABC
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 the STATUS Coulmn of XYZ to DEL for the combination of above mentioned duplicates
For example shown above
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?
I have written SQL as
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.
|
|
|
Re: Update Query [message #308375 is a reply to message #308368] |
Mon, 24 March 2008 02:58  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Duplicates questions have been asked so many times thet are no more Expert ones.
If you don't know the level of your question, then it is a newbie one.
By the way, '01-OCT-2007' is a string not a date.
Regards
Michel
|
|
|