question

Yahya-3144 avatar image
0 Votes"
Yahya-3144 asked EchoLiu-msft edited

Skipping null columns from source table in update

Hi

I need to update rows from one table to another while matching key values in rows in both tables.

The issue is that some columns in source table might be empty and I don't want these empty columns from source table to overwrite columns in destination table. How do I do that?

In theory I can update one row at a time if that would help. I just don't know beforehand what source columns would contain data in each row as it is being entered online by users.

Thanks

Regards

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you have any update?Did the following methods solve your problem?If not, please provide more details.
If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
Nollagabril-7967 avatar image
0 Votes"
Nollagabril-7967 answered

or use the coalesce function


 update t1 set t1.col1 = coalesce(t2.col1, t1.col1) 
 from t1 inner join t2 on t1.Joincol1 = t2.joincol1


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Yahya-3144,

Please refer to:

 UPDATE t1
 SET t1.col=CASE WHEN t2.col is not null THEN t2.col ELSE t1.col END
 FROM t1
 JOIN t2 ON t1.id=t2.id

Or:

 UPDATE t1
 SET t1.col=t2.col
 FROM t1 
 JOIN t2 ON t1.id=t2.id AND t2.col is not null

For more details, please refer to the following example:

 CREATE table #sourcetable(No1 int , Name char(15))
 INSERT INTO #sourcetable VALUES(1,'nn'),(2,null),(3,'name3')
    
 CREATE table #destinationtable (No1 int , Name char(15))
 INSERT INTO #destinationtable VALUES(1,'nn'),(2,'name1'),(3,'name2')
    
 SELECT * FROM #sourcetable
 SELECT * FROM #destinationtable
    
 UPDATE d
 SET d.Name=CASE WHEN s.[Name] is not null THEN s.[Name] ELSE d.[Name] END
 FROM #destinationtable d 
 JOIN #sourcetable s ON d.No1=s.No1
    
 SELECT * FROM #destinationtable

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.