Replacing null values with join

Question

I have two tables

      Table1:                                Table2:
ID:      Type:     Amount:               ID:       Amount:
165      Red         300                 188       425           
167      Red         100                 189       500 
168      Blue        250                 222       129
188      Grey        NULL                333       247 
189      Grey        NULL                369       328

I am trying to replace the null values from table 1 by joining on table 2.

My code results in two amount columns.

LEFT JOIN table2 
ON table2.pk = table1.pk
AND table1.Type IS NULL

Gordon Linoff · Accepted Answer · 2019-05-06 22:05:35Z

2

I think you want:

select t1.id, t1.type, coalesce(t1.amount, t2.amount)
from table1 t1 left join
     table2 t2
     on t1.id = t2.id;

answered May 6, 2019 at 22:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Sowbarani Karthikeyan · Accepted Answer · 2019-05-07 07:09:29Z

0

Hope this helps for your needs

select t1.id,t1.type,isnull(t1.amount,t2.amount) 'Amount' from table1 t1 left join  table2 t2 on t1.id = t2.id

you can use isnull() function so that if the first table amount is null then it will take second table value.

answered May 7, 2019 at 7:09

Sowbarani Karthikeyan

1611 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

Replacing null values with join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related