hadi.k hadi.k - 7 months ago 17
SQL Question

Replace Data With Key on 2 table in SQL Server

My question in kind of replace with key in SQL Server. Can anyone give me a query to do this?

Thanks for your answers!

Table1
:

ID | Code | Des | more columns
---+------+-----+-------------
1 | 100 | a | ...
2 | 200 | b | ...
3 | 300 |data3| ...


Table2
:

ID | Code | Des
---+------+------
1 | 100 | data1
2 | 200 | data2


The result must be this:

ID | Code | Des | more columns
---+------+-----+-------------
1 | 100 |data1| ...
2 | 200 |data2| ...
3 | 300 |data3| ...

Answer

Do a LEFT JOIN, if there are no table2.Des value, take table1.Des instead:

select t1.ID, t1.Code, coalesce(t2.Des, t1.Des), t1.more Column
from table1 t1
left join table2 t2 on t1.code = t2.code

Or, perhaps you want this:

select * from table2
union all
select * from table1 t1
where not exists (select 1 from table2 t2
                  where t2.code = t1.code)

I.e. return table2 rows, and if a code is in table1 but not in table2, also return that row.