Update table for multiple rows with different values in one query

Transact-SQL
, ,
1

I have a number of records that need to be updated in a particular table (i.e TableDst). Specifically, three columns need to be updated. The values will not be the same for each row and said values come from another table (i.e. TableSrc).

Example:

TableDst

C1		C2		C3		C4
null		null		null		1005		
null		null		null		2008
null		null		null		3045
null		null		null		4919
null		nlll		null		2931

TableSrc

C1		C2		C3		C4
john		100		D1		1005		
jerry		200		D2		2008
kate		300		D4		3045
laruen		302		L               4919
tyler		499		M08		2931

In this example, I want to copy columns C1 through C3 from TableSrc to TableDst, where TableSrc.C4 = TableDst.C4. The result would resemble:

TableDst

C1		C2		C3		C4
john		100		D1		1005		
jerry		200		D2		2008
kate		300		D4		3045
laruen          302	        L               4919
tyler		499		M08		2931

I could loop through each row and update. But I was hoping to avoid writing a stored procedure. I'm thinking there must be a faster, easier, more efficient way. Is there some way I can realize the above with one query or is this not possible and I have to write a stored procedure?

Thank you very much.

2

You should be able to do the update using the following:

UPDATE d SET
	C1 = s.C1,
	C2 = s.C2,
	C3 = s.C3
FROM
	TableDst d
	INNER JOIN TableSrc s ON
		s.C4 = d.C4;
1 Like
3

Thank you so much. Very simple!