Replace Data With Key on 2 table in SQL Server

Question

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

It's not clear what you want. The column 'Des' within the result should it report the corresponding value within the second table? — Robert Kock
– Robert Kock, Commented Apr 19, 2016 at 10:14
@RobertKock if code common in 2 table , value of Des is from table 2 but if code not common in 2 table , value of Des is from table 1 — hadi.k
– hadi.k, Commented Apr 19, 2016 at 10:17
Add ID = 4 to table2, and have different code values. (Or are code the join columns?) — jarlh
– jarlh, Commented Apr 19, 2016 at 10:18
@jarlh yea table 2 is kind of reformation table , where the Desc in table 2 must replace in table 1 where t1.Code=t2.Code — hadi.k
– hadi.k, Commented Apr 19, 2016 at 10:22

jarlh · Accepted Answer · 2016-04-19 10:22:06Z

2

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.

edited Apr 19, 2016 at 10:22

answered Apr 19, 2016 at 10:16

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

2 Comments

Ullas Over a year ago

Should be coalesce(t2.Des, t1.Des) instead of coalesce(t2.Des, t2.Des)

jarlh Over a year ago

@hadi.k, just curious, which one did you chose?

Ullas · Accepted Answer · 2016-04-19 10:14:31Z

2

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;

edited Apr 19, 2016 at 10:14

answered Apr 19, 2016 at 10:13

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Comments

Robert Kock · Accepted Answer · 2016-04-19 10:27:09Z

2

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

answered Apr 19, 2016 at 10:27

Robert Kock

6,0482 gold badges14 silver badges23 bronze badges

Comments

Rich Benner · Accepted Answer · 2016-04-19 10:19:53Z

0

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

answered Apr 19, 2016 at 10:19

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Collectives™ on Stack Overflow

Replace Data With Key on 2 table in SQL Server

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related