| updating a field using a join [message #373631] |
Fri, 27 April 2001 13:15  |
Josh Licht
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
I have a problem: I have the following two tables and I want to update a column in table b with a column in table c, based upon two fields that are equal between the tables and I'm getting a invalid column name error.
Here are my tables and sql, any help would be appreciated. Thanks.
--- Table B
user_id (pk)
username
-----Table c
item_id (pk)
name
modified_by
user_id (Just Added)
Query:
Update C
Set c.user_id = b.user_id
where c.modified_by = b.username
-----This produces the invalid column error--------
|
|
|
|
| Re: updating a field using a join [message #373633 is a reply to message #373631] |
Fri, 27 April 2001 14:08  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this:
SET SERVEROUT ON
DECLARE
v_user_id VARCHAR2(15);
v_username VARCHAR2(30);
BEGIN
CURSOR CURSOR_NAME IS
select username,
user_id
from B;
BEGIN
OPEN CUROR_NAME;
LOOP
FETCH CURSOR_NAME into v_username,
v_user_id;
EXIT WHEN C1%NOTFOUND;
UPDATE C
SET user_id = v_user_id
WHERE modified_by = v_username;
END LOOP;
CLOSE CURSOR_NAME;
END;
/
HTH,
--Cindy
|
|
|
|
| Re: updating a field using a join [message #373636 is a reply to message #373633] |
Fri, 27 April 2001 14:41 |
cl
Messages: 10
Registered: April 2001
|
Junior Member |
|
|
OR this:
update C
set user_id = (select b.user_id from B b, C c where c.modified_by = b.username);
If you have problem with the above statement...updating more data then it supposed to, then try this:
update C
set user_id = (select b.user_id from B b, C c where c.modified_by = b.username)
where modified_by = (select username from B); -- I don't think this is necessary...just redundancy, so try it only if you have problem from above. However, I believe you will get the same results from all these examples, unless there are two or more people that have the same username with different user_id then you made need to add more conditions in your update statement.
--Cindy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
