Oracle Update one table from another table with join

I have an Oracle Table for Project
    Project_Id  Project name  ProjManger   User_ID
(User ID column is blank now.All other fields are filled)

I have user table
   User_Id   UserName

I need a query to update the User_ID in project table with User_ID in User table where Project.ProjManager = User.Username
Sam OZAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
CREATE TABLE Projects
    (Project_Id int, ProjectName varchar2(8), ProjManger varchar2(4), User_ID int)
;

INSERT ALL 
    INTO Projects (Project_Id, ProjectName, ProjManger, User_ID)
         VALUES (1, 'ProjectX', 'Paul', NULL)
SELECT * FROM dual
;

CREATE TABLE Users
    (ID int, NAME varchar2(4))
;

INSERT ALL 
    INTO Users (ID, NAME)
         VALUES (201, 'Paul')
SELECT * FROM dual
;

Open in new window

update projects p
set p.User_ID = (select u.id from users u where p.ProjManger = u.name)
where p.User_ID IS NULL
;

select p.*from projects p;

| PROJECT_ID | PROJECTNAME | PROJMANGER | USER_ID |
|------------|-------------|------------|---------|
|          1 |    ProjectX |       Paul |     201 |

Open in new window

see http://sqlfiddle.com/#!4/47b9f/2
0
 
awking00Connect With a Mentor Commented:
You could also use the merge function -
merge into project p
using user u
on (p.projmanager = u.username)
when matched then
update set p.user_id = u.user_id;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.