Compare two text columns from two different tables row wise in SQL server

Question

I have two tables like below:

Now I want the amt column from the 1st table to be populated in the 2nd table in when all the columns of second table is a subset of first table. To clarify, here in this example above, the first rows will be populated because all the columns of table 2 is a subset of table 1 but in the last 'c4' is not matching with the element of table 1 (the same row), hence that row 'amt' column will be blank.

I need to solve it i sql (Microsoft SQL server 2014)

Any leads will be appreciated

Answer 1

I think this is what you want:

select t2.*, t1.amt
from table2 t2 left join
     table1 t1
     on (t2.a = t1.a or t2.a is null) and
        (t2.b = t1.b or t2.b is null) and
        (t2.c = t1.c or t2.c is null) and
        (t2.d = t1.d or t2.d is null);

You can readily turn this into an update:

update t2
    set amt = t1.amt
from table2 t2 left join
     table1 t1
     on (t2.a = t1.a or t2.a is null) and
        (t2.b = t1.b or t2.b is null) and
        (t2.c = t1.c or t2.c is null) and
        (t2.d = t1.d or t2.d is null);