CODEWITHSUNDEEP
sqlsql-servert-sql
Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Update based on another table

I have this following table (Employees) :

EmployeeId WorkingForId WorkingFor
34         45           NULL
34         66           NULL
34         78           NULL

I want to update the WorkingFor column based on another table (Companies) like below : 

CompanyId Name
45        SONY
66        HP
78        MICROSOFT
99        APPLE

So my expected output will be like below : 

EmployeeId WorkingForId WorkingFor
    34         45       SONY
    34         66       HP
    34         78       MICROSOFT

You can create this table by code below :

CREATE TABLE employees
    ([EmployeeId] int, [WorkingForId] int, [WorkingFor] varchar(40))
;

INSERT INTO employees
    ([EmployeeId], [WorkingForId], [WorkingFor])
VALUES
    (34, 45, NULL),
    (34, 66, NULL),
    (34, 78, NULL)
;


CREATE TABLE companies
    ([CompanyId] int, [Name] varchar(9))
;

INSERT INTO companies
    ([CompanyId], [Name])
VALUES
    (45, 'SONY'),
    (66, 'HP'),
    (78, 'MICROSOFT'),
    (99, 'APPLE')
;

Upvotes: 0

Views: 63

Answers (2)

Amirhossein
Amirhossein

Reputation: 1251

You can use this simple Update statment :

Update employees 
Set WorkingFor = c.Name
From companies c
Where CompanyId = employees.WorkingForId

Out put :

EmployeeId  WorkingForId    WorkingFor
34          45              SONY
34          66              HP
34          78              MICROSOFT

Upvotes: 1

forpas
forpas

Reputation: 164184

You can do it with a join in the UPDATE statement:

update e
set e.WorkingFor = c.Name
from employees e inner join companies c
on c.CompanyId = e.WorkingForId

See the demo.
Results:

> EmployeeId | WorkingForId | WorkingFor
> ---------: | -----------: | :---------
>         34 |           45 | SONY      
>         34 |           66 | HP        
>         34 |           78 | MICROSOFT

Upvotes: 2

Related Questions