Home » SQL & PL/SQL » SQL & PL/SQL » Update Query (Oracle 9.1)
Update Query [message #308368] Mon, 24 March 2008 02:42 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: UPPER(STRING)
Next Topic: column format
Goto Forum:
  


Current Time: Sat Dec 10 18:11:57 CST 2016

Total time taken to generate the page: 0.10887 seconds