Hi,

I am trying to run an update query against a limited number of records in a table where a corresponding record exists in another table.


update agency_cd_ctrl a
set a.user_id = (select b.user_id
from seller_imp b
where a.user_id = b.alias_user_id)

The problem i am having is that the code is updating all records in the update table (sets user_id to null where there is no match) - i was hoping just to update the records where there was a match (about 60 records out of 3000) and update to the value from the 2nd table.

Can anyone point me in the right direction please?

Thanks
James

this is the ANSI SQL forum

first thing you have to do is tell us which database system you're using

UPDATE syntax varies too much from one to the next

once we know which database you're using, we'll move this thread to the appropriate forum and you can get the specific help you need

i'm going to guess SQL Server 2000

As r937 pointed out, it would help to know which SQL data engine you are using. The UPDATE statement has a lot of vendor/dialect specific add-on features that allow for more concise coding than the ANSI standard permits.

To use the pure ANSI SQL to get what you've described:-PatP

Hi,

Thanks for the response - i'm trying to run the query against an Oracle 9i database.

The code below did the trick - thanks for your help...

James


update agency_cd_ctrl
set user_id = (select b.user_id
from seller_imp b
where b.alias_user_id = agency_cd_ctrl.user_id)
where exists (select *
from seller_imp c
where c.alias_user_id = agency_cd_ctrl.user_id)