Home » SQL & PL/SQL » SQL & PL/SQL » update the table issue
update the table issue [message #225477] Tue, 20 March 2007 01:10 Go to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
HI there,

I am having problem in updating the table.
I am having two tables join and want to update the second table.
Here is the script i have written

update test_1 v
set v.cust_fk = 141
where v.dsi_key_id in (
select v.dsi_key_id
from test_2 a ,test_1 v
where a.TARGET_NAME = v.TARGET_NAME
and a.POLL_FLAG = 0
and v.TARGET_NAME = 'm-test-sydney-router');

What i want is if POLL_FLAG =0 in test_2 a . Update the column cust_fk with 141 in test_1 v.

But the problem is the script is updating all the fields in the table to cust_fk = 141. Total rows updated are 2000 instead of 7 rows should effect as node 'm-test-sydney-router' has only 7 rows.
i assume It is not taking the where clause POLL_FLAG=0.

Please help.

Rgds
Dil
Re: update the table issue [message #225487 is a reply to message #225477] Tue, 20 March 2007 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try one of the followings (depending on your keys):

update 
 (select v.dsi_key_id, v.cust_fk
  from test_2 a ,test_1 v
  where a.TARGET_NAME = v.TARGET_NAME 
    and a.POLL_FLAG = 0
    and v.TARGET_NAME = 'm-test-sydney-router')
set cust_fk = 141
/

update test_1 v
set v.cust_fk = 141
where v.dsi_key_id in 
        ( select v.dsi_key_id 
          from test_2 a
          where a.TARGET_NAME = v.TARGET_NAME 
            and a.POLL_FLAG = 0 )
  and v.TARGET_NAME = 'm-test-sydney-router'
/

Regards
Michel
Re: update the table issue [message #225674 is a reply to message #225487] Tue, 20 March 2007 18:55 Go to previous message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Michel,

Thanks for your reply.

It works fine.
Warm Regards
Dil
Previous Topic: inserting multiple rows in single query..
Next Topic: NEW Keyword
Goto Forum:
  


Current Time: Sun Dec 04 10:44:26 CST 2016

Total time taken to generate the page: 0.07398 seconds