How to compare two tables and replace nulls with values from other table in SQL Server/Azure SQL

Question

I have two tables with the same structure but slightly different values. If a record of table 1 has some column values as null then that has to update to value in table 2, vice-versa.

Table 1

+--------------------+
|  id | exp | last   |
+--------------------+
| 1  | null | 4005   |
| 2  | null | null   |
| 3  | 10/19  | 1001 |
+--------------------+

Table 2

+-------------------+
|  id | exp | last  |
+-------------------+
| 1  | 08/23 | null |
| 2  | 07/21 | 3867 |
| 3  | null  | 1001 |
+-------------------+

Required Output

Table 3

+--------------------+
|  id | code | last  |
+--------------------+
| 1  | 08/23 | 4005  |
| 2  | 07/21 | 3867  |
| 3  | 10/19  | 1001 |
+--------------------+

Is this an outer join and if so how would I do this on SQL Server/Azure SQL?

forpas · Accepted Answer · 2019-04-22 12:52:33Z

1

select t1.id,
case when t1.exp is null then concat(t1.exp, t2.exp)  when t2.exp is null then concat(t1.exp, t2.exp) 
when t1.exp is not null then (t1.exp) when t2.exp is not null then (t2.exp)  end as exp,
case when t1.last is null then concat(t1.last, t2.last) 
when t2.last is null then concat(t1.last, t2.last) when t1.last is not null then (t1.last) 
when t2.last is not null then (t2.last) end as last
from Table1 t1 join Table2 t2 on t1.id=t2.id

edited Apr 22, 2019 at 12:52

forpas

165k10 gold badges51 silver badges85 bronze badges

answered Apr 22, 2019 at 12:44

Ravi Sharma

3801 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pratik Bhavsar · Accepted Answer · 2019-04-22 08:37:01Z

0

A simple update statement with CASE or IIF will do:

UPDATE t1 
    IIF(t1.exp IS NULL, t2.exp, t1.exp)
    IIF(t1.last IS NULL, t2.exp, t1.exp)
FROM
    Table1 t1 
LEFT JOIN Table2 t2 
    ON t1.id = t2.id 

UPDATE t2
    IIF(t2.exp IS NULL, t1.exp, t2.exp)
    IIF(t2.last IS NULL, t1.exp, t2.exp)
FROM
    Table2 t2 
LEFT JOIN Table1 t1
    ON t1.id = t2.id

answered Apr 22, 2019 at 8:37

Pratik Bhavsar

8481 gold badge10 silver badges35 bronze badges

Comments

forpas · Accepted Answer · 2019-04-22 08:59:29Z

0

With coalesce():

select 
  t1.id,
  coalesce(t1.exp, t2.exp) exp,
  coalesce(t1.last, t2.last) last,
from table1 t1 inner join table2 t2
on t2.id = t1.id

this gives precedence to the table1 values if they are not null.
Also if there is a case that the number of rows in the 2 tables is not the same, then you should use a left join from the table with the most rows to the other.
If you want to insert these rows to table3:

insert into table3 (id, exp, last)
select 
  t1.id,
  coalesce(t1.exp, t2.exp) exp,
  coalesce(t1.last, t2.last) last,
from table1 t1 inner join table2 t2
on t2.id = t1.id

edited Apr 22, 2019 at 8:59

answered Apr 22, 2019 at 8:52

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

How to compare two tables and replace nulls with values from other table in SQL Server/Azure SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related