Home » SQL & PL/SQL » SQL & PL/SQL » Rewriting Query
Rewriting Query [message #318582] Wed, 07 May 2008 03:51 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Could you please tell me how to write this query better?
 UPDATE POB_VENDOR_CLAIM
   SET POB_VENDOR_CLAIM_STATUS = 20,
       POB_EMAIL_SENT = 0   
 WHERE POB_VENDOR_CLAIM_ID = 10020 AND
 (SELECT 1
   FROM
     POB_VENDOR_CLAIM_APPROVAL
   WHERE
     POB_VENDOR_CLAIM_ID = 10120 AND
     POB_VENDOR_CLAIM_STATUS = 40)


I wish to update status to 20 for claim id 10020 and if there are any records in pob_vendor_claim_approval table for the same claim id and claimstatus is 40 there.
Re: Rewriting Query [message #318585 is a reply to message #318582] Wed, 07 May 2008 04:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By 'Better', you mean 'so that it doesn't error' I presume Cool

You just need to add the EXISTS keywork before your subquery/
Re: Rewriting Query [message #318588 is a reply to message #318582] Wed, 07 May 2008 04:12 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Try the query by joining columns (of two tables say A, B), as follows

 UPDATE POB_VENDOR_CLAIM A
   SET POB_VENDOR_CLAIM_STATUS = 20,
       POB_EMAIL_SENT = 0   
 WHERE POB_VENDOR_CLAIM_ID = 10020 AND
 EXISTS
 (SELECT *
   FROM
     POB_VENDOR_CLAIM_APPROVAL B
   WHERE
     B.POB_VENDOR_CLAIM_ID = A.POB_VENDOR_CLAIM_ID AND
     B.POB_VENDOR_CLAIM_STATUS = 40)


Regards,

MSMallya
Re: Rewriting Query [message #318590 is a reply to message #318588] Wed, 07 May 2008 04:25 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why doing a join when the value is a constant?
It is far better to not join.

Regards
Michel
Previous Topic: Irregular nature of Order By Clause
Next Topic: refcursors
Goto Forum:
  


Current Time: Fri Dec 02 20:34:28 CST 2016

Total time taken to generate the page: 0.14065 seconds