CODEWITHSUNDEEP
sqlsql-server
hadi.k
hadi.k

Reputation: 25

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| ...

Upvotes: 2

Views: 73

Answers (4)

Robert Kock
Robert Kock

Reputation: 6018

Try this:

SELECT          Table1.ID,
                Table1.Code,
                ISNULL(Table2.Des, Table2.Des) AS Des
FROM            Table1
LEFT OUTER JOIN Table2
             ON Table1.Code = Table2.Code;

You said "if code common in 2 table" so join on the code and not on the ID

Upvotes: 2

jarlh
jarlh

Reputation: 44776

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.

Upvotes: 2

Rich Benner
Rich Benner

Reputation: 8113

Ok, so you want all results from Table1 but to use the value in 'Des' when it's available from Table2? You'd want to do something like this;

SELECT a.ID
    ,b.Code
    ,ISNULL(b.Des,a.Des) AS Des
FROM Table1 a
LEFT JOIN Table2 b ON a.ID = b.ID

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Use JOIN.

Query

SELECT t1.ID, t1.Code, 
CASE WHEN t1.Des LIKE 'data%' THEN t1.Des ELSE t2.Des END AS Des
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.ID = t2.ID;

Upvotes: 2

Related Questions