SQLTeam.com | Weblogs | Forums

Update table with foreign key


#1

I created a base table called SystemUsers and populated it with a few columns from the Recruiters table as below

insert into SystemUsers (lastname,email,password,datelastloggedin,firstname)
select  R.LastName, R.email, R.[password], R.DateLastLoggedIn, R.FirstName
from recruiters R 
left join SystemUsers SU ON SU.email = R.Email where su.email is null

I want to get the SystemUserId from the SystemUsers table and populate it in the SystemUserId column in the recruiter table as it's a foreign key.

How do I do this? I want to do this...Update Recruiters set SysUsersId column with SysUsersId column in SysUsers table where the SU.email = R.email


#2

image


#3
UPDATE R
SET SystemUserId = SU.SystemUserId
FROM dbo.recruiters R
INNER JOIN dbo.SystemUsers SU ON SU.email = R.Email
WHERE R.SystemUserId IS NULL

#4

Thanks! Thats perfect