CODEWITHSUNDEEP
sql
stefanR
stefanR

Reputation: 89

SQL update from other table when value is NULL

I have the following problem: there is a table "orders" that contains all customer orders. There are however many values in the column "emloyee_supervisor" (ID of the supevisor of the employee who did the transaction) NULL. The information on that is stored in a table on the emplyoees themselves, "employees", in a column called "supervisor". Now i want to update the NULL (that is take the value from "employees" to "orders"). How can i do that?

The key of an employee is the employee_id (used as key in "employees"), the key of an order is order_id.

I am using DBvisualizer.

I am not allowed to share the real data, but this is what the input conceptually looks like:

enter image description here

This is the desired output ("employees" table of course not changed)

enter image description here

Upvotes: 0

Views: 102

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your question is not tagged with the database you are using. An answer that should work in any database is:

update orders
    set employee_supervisor = 
            (select e.supervisor
             from employees e 
             where e.employee_id = orders.employee_id
            )
where orders.employee_supervisor is null;

Upvotes: 0

Jayrag Pareek
Jayrag Pareek

Reputation: 374

try this:

update o
set o.emloyee_supervisor = e.supervisor 
from orders o
inner join employees e on e.employee_id = o.employee_id
where o.emloyee_supervisor is null

Upvotes: 1

Karan
Karan

Reputation: 12619

Try like below query.

update o
set o.emloyee_supervisor = e.supervisor
from orders o
inner join employees e
    on e.employee_id = o.employee_id
where o.emloyee_supervisor is null

Upvotes: 1

Related Questions