SQL Server update and replace values with values from another table

Question

I have two tables:

Connections:

id  user_id  connection_info
------------------------------
 1    1      ...
 2    1      ...
 3    2      ...

Lists:

id  connection_id  name
-----------------------
1        1         ...
2        2         ...
3        1         ...

I currently have user_id's in the lists.connection_id column. I would like to join the lists table with the connections table by connections.user_id = lists.connection_id and then replace the lists.connection_id with the corresponding id from the connections table.

so did you try something and fail? if so share your code/errors please. if not, attempt a solution and share it to give folks something to work with. — Tanner
– Tanner, Commented Oct 5, 2017 at 9:17

Rigerta · Accepted Answer · 2017-10-05 09:23:05Z

1

You could use UPDATE FROM like this:

update l
set l.connection_id = c.id 
from connections c join lists l on c.user_id = l.connection_id

Initially you would want to test what you are going to update, running a SELECT statement:

select l.connection_id as con_old
     , c.id as con_new
     , ... (other cols you might want to check)
from connections c join lists l on c.user_id = l.connection_id

answered Oct 5, 2017 at 9:23

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

SQL Server update and replace values with values from another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related