Home » SQL & PL/SQL » SQL & PL/SQL » Question on update table
Question on update table [message #254469] Thu, 26 July 2007 18:00 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

I am trying to come up with a sql statement to update a table based on the data values from multiple tables. Here is the data I am looking at:

SQL> select ap.APLCTN_ID,ap.BIDDER_NUM,ap.APLCTN_STUS_CD, doc.FORMA_DCMTN_RCPT_STUS_CD
  2  from supplier_application ap, supplier_documentation doc
  3  where ap.BIDDER_NUM=doc.BIDDER_NUM and
  4  ap.APLCTN_STUS_CD='02' and
  5  doc.FORMA_DCMTN_RCPT_STUS_CD='02';

 APLCTN_ID BIDDER_ AP FO
---------- ------- -- --
         0 1000829 02 02
         0 1000822 02 02
         0 1000843 02 02
         0 1000035 02 02


So, these 4 rows are what I expect that my update should take place with the following statment:

SQL> update supplier_application A
  2  set APLCTN_STUS_CD = '04'
  3   where A.APLCTN_ID in
  4    (select ap.APLCTN_ID
  5      from cbssr.supplier_application ap,
  6         cbssr.supplier_documentation doc
  7      where ap.APLCTN_STUS_CD = '02' and
  8         ap.bidder_num = doc.bidder_num and
  9         doc.forma_dcmtn_rcpt_stus_cd = '02'
 10  );

231 rows updated.


As you can see I got 231 rows updated, instead of 4 rows. Why did I do that is wrong?

Thanks for your advice.
sy
Re: Question on update table [message #254472 is a reply to message #254469] Thu, 26 July 2007 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Why did I do that is wrong?

Not use the same SQL in the 2nd case as in the 1st.
Re: Question on update table [message #254478 is a reply to message #254469] Thu, 26 July 2007 20:19 Go to previous message
syang
Messages: 30
Registered: February 2007
Member
I think I got it "update" right this time.

SQL> update supplier_application A
  2  set APLCTN_STUS_CD='04'
  3   where
  4    A.bidder_num in
  5     (select ap.bidder_num
  6       from
  7        supplier_application ap,
  8        supplier_documentation doc
  9          where
 10           ap.bidder_num=doc.bidder_num and
 11           ap.APLCTN_STUS_CD='02' and
 12           doc.forma_dcmtn_rcpt_stus_cd = '02'
 13       );

4 rows updated.


As a result of the update, only rows that satisfy the conditions got updated. See below:

SQL> select bidder_num, APLCTN_STUS_CD from supplier_application where APLCTN_STUS_CD = '04';

BIDDER_ AP
------- --
1000829 04
1000822 04
1000843 04
1000035 04


Thanks!
sy
Previous Topic: packages.
Next Topic: Query to retrieve a letter
Goto Forum:
  


Current Time: Wed Dec 07 08:53:51 CST 2016

Total time taken to generate the page: 0.16811 seconds