question

binodbabu avatar image
binodbabu asked

update table


create table #a(col1 char(2), col2 int)
insert into #a values('a',1)
insert into #a values('b',2)
insert into #a values('c',3)
insert into #a values('d',4)
insert into #a values('e',5)
insert into #a values('f',6)

create table #b(col1 char(2), col2 int)
insert into #b values('a',1)
insert into #b values('b',2)
insert into #b values('c',3)
insert into #b values('x',4)
insert into #b values('y',5)
insert into #b values('z',6)


update  #a
set #a.col1= #b.col2 from #a  join #b on #a.col1=#b.col1

-- it affects ony 3 rows.. i need remaining 3 rows as null)
Words are not enough to say thanks to asksqlservercentral team. thanks in advance.
tsqlupdate
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

binodbabu avatar image binodbabu commented ·
I need something like this.. 

if exists (select b.col2 
           from #a a 
           join #b b on a.col1=b.col1)

 update #a 
 set #a.col1 = b.col2 
 from #a a 
 join #b b on a.col1=b.col1

else 

 update #a set #a.col1= null
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
Does this work for you? UPDATE a SET a.col1 = CASE WHEN a.col1 = b.col1 THEN b.col2 ELSE NULL END FROM #a a LEFT JOIN #b b ON a.col1 = b.col1 Thanks!
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
the `CASE` statement is not necessary.. The only thing needed is the `LEFT JOIN` as in case corresponding record from right table doesn't exists then NULL will be stored into the #a.col1 update #a set #a.col1= #b.col2 from #a LEFT join #b on #a.col1=#b.col1
4 Likes 4 ·
robbin avatar image robbin commented ·
@Pavel Pawlowski is right. +1 for that. @binodbabu An equivalent to that... update #a set #a.col1= (select #b.col2 from #b WHERE #a.col1=#b.col1) I have mentioned the use of subquery, in case, you are not making join on the unique columns. The update with LEFT JOIN could result in something undesired, but subquery will fail For example create table #a(col1 char(2), col2 int) insert into #a values('a',1) insert into #a values('b',2) insert into #a values('c',3) insert into #a values('d',4) create table #b(col1 char(2), col2 int) insert into #b values('a',NULL) --TO BE DUPLICATED insert into #b values('b',2) insert into #b values('c',3) insert into #b values('x',4) insert into #b values('a',8)----DUPLICATED insert into #b values('a',1)----DUPLICATED update #a set #a.col1= (select #b.col2 from #b WHERE #a.col1=#b.col1) update #a set #a.col1= #b.col2 from #a LEFT join #b on #a.col1=#b.col1 SELECT * FROM [#a] AS A DROP TABLE [#a],[#b] The JOIN method will silently update the table #a 's column value with any value of the table #b's column, whereas, the subquery method will fail to update.
1 Like 1 ·
binodbabu avatar image binodbabu commented ·
yes that worked thank you very much john morehouse
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Awesome! Glad to know that it worked. Can you please select my solution as your answer so that others know that you've found something that works? Thanks!
0 Likes 0 ·
JohnM avatar image JohnM commented ·
DOH!! I missed the simple answer!! ;-) If it had been a snake, it would have bitten me.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.