CODEWITHSUNDEEP
sqlsql-servernullsql-update
vicky
vicky

Reputation: 279

Update null values by merging another table in mssql

I have two tables like :

Table 1:

Customer    employee
ASD_1234    WF001
ASD_1235    WF002
ASD_1236    WF003
ASD_1237    NULL
ASD_1238    NULL
ASD_1239    NULL
ASD_1240    WF004
ASD_1234    WF001
ASD_1236    WF003
ASD_1240    WF004

Table2:

Customer    com_employee
ASD_1234    WF001
ASD_1235    WF002
ASD_1236    WF003
ASD_1237    WF005
ASD_1238    WF006
ASD_1239    WF007
ASD_1240    WF004

The table 2 is the metadata which consists of unique Customer.Now I need to update only the null values in table1 by joining table2 on Customer. How can I achieve this? Thanks

Upvotes: 1

Views: 2408

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272066

You can use a correlated query:

UPDATE table1
SET employee = (
    SELECT com_employee
    FROM table2
    WHERE table2.customer = table1.customer
)
WHERE employee IS NULL

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

We can try using an updatable CTE here:

WITH cte AS (
    SELECT t1.employee, t2.com_employee
    FROM Table1 t1
    INNER JOIN Table2 t2
        ON t1.Customer = t2.Customer
)

UPDATE cte
SET employee = com_employee
WHERE employee IS NULL;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311143

You could use an update-join statement with a condition on the nullibility of the employee:

UPDATE t1
SET    t1.employee = t2.com_employee
FROM   t1
JOIN   t2 ON t1.customer = t2.customer
WHERE  t1.employee IS NULL

Upvotes: 0

Related Questions