How to copy information in SQL from one table to another

I need to build a query to copy information in a column from one table to a column in another table.

This is how the tables looks like:

People:

PersonId	Name	StatusId
1	John
2	Jenny
3	Steve

Assignments:

AssignmentId	Country	PersonId
1	UK.	1
2	USA	3

Status:

StateId	Name
1	Busy
2	Free

There is a relationsihp between the People and Assignments tables: PersonId on the Assignments table is a FK. The People table has a relationship with the Status table through the FK StatusId. What I need to do is populate the StatusId on the table People with the StatusId from the table Status if the person in the table People exists on the table Assignments. On the sample above both John and Steve are in the Assignments table, in this case theirs StatusId on the table People should be set to 1.

I was trying to do it with this:

update People 
set StatusId = 1 
where PersonId IN (
    select PersonId 
    from Assignments 
    where Assignments.PersonId = People.PersonId
)

but as you can see I am hardcoding the StatusId what will not works. Is there some way to get the StatusId based on the result of the select? Or is there another way to get the StatusId?

Answer

If you want to refer to it by “name”, you can use a subquery:

update People
    set StatusId = (select s.StatusId from status s where name = 'Busy')
    where PersonId IN (select a.PersonId from Assignments a where a.PersonId = People.PersonId);

Advertisement

Answer