Update Table.Column based on another Table 1

[cwsstins]

I need a statement to update a field on one table based on data found in a field on another table. The user login ID can be found on the Tracker table. I would like to populate the user login ID on a new field I just created on the Assignee table.

Tracker loginID Assignee heatlogin
John Smith SMITHJOH John Smith
Jill Hope HOPEJIL Jill Hope
Larry Fine FINELAR Larry Fine

First I tried:

UPDATE assignee
SET heatlogin = tracker.loginid
from assignee, tracker
where assignee.assignee = tracker.fullname

This gave me an error "SQL command not properly ended"

So I tried:

update ASSIGNEE
set HEATLOGIN = 
(
select TRACKER.LOGINID from TRACKER
where TRACKER.FULLNAME = ASSIGNEE.ASSIGNEE
);

But that gives me the message "single row sub-query returns more than one row"

So it seems like that I need some addition to this last statement that will enable the multiple records from the sub-query to populate the field...what am I missing?

 

[sem]

Actually that means that multiple TRACKER.LOGINID has been retured for ASSIGNEE.HEATLOGIN field and you must specify which one to choose. You may either add some aggregating function (like max or min) or just add ROWNUM=1 condition in case you're sure that they're all the same.

Regards, Dima

http://seminihin.narod.ru

 

[cwsstins]

Isn't there some way to populate this data for all the rows at once? I've been successful in running the query to update a single record at a time using a named row "assignee = John Smith" but this is time-consuming. There must be some way to make the change for all records. I seem to remember doing this a while ago (while testing for another change), but I looks like I didn't save the query.

stinsman

 

[sem]

Again, your code is almost correct, i.e. it updates ALL rows in ASSIGNEE but providing that for each row in ASSIGNEE only one value from TRACKER is selected. According to that error it's clear that for some records more than 1 row is returned.

Regards, Dima

http://seminihin.narod.ru

 

[cwsstins]

<light bulb>

AH!!!!!!!!!!!!!!!!! Now I see what you mean. Thanks, that was driving me nuts!

stinsman