SQL Server: How to update a table with values from another table

Question

I have two tables. Users1:

UserID HolidayCity ValidFrom    ValidTo
1      NULL        '1900-01-01' '2017-05-09'
1      NULL        '2017-05-09' NULL
2      NULL        '1900-01-01' '2017-05-09'
2      NULL        '2017-05-09' NULL

Users2:

UserID CityID
1      33
2      55

I need to update HolidayCity column from the first table with the values in CityID column from the second table for each UserID, but only those records, where ValidTo IS NULL, so that the resulting table Users1 would be:

UserID HolidayCity ValidFrom    ValidTo
1      NULL        '1900-01-01' '2017-05-09'
1      33          '2017-05-09' NULL
2      NULL        '1900-01-01' '2017-05-09'
2      55          '2017-05-09' NULL

Can you please tell me how to do that?

Solution 1

This is a simple inner join

update u
  set u.holidaycity=c.cityid
from users1 u
  inner join users2 c
    on u.userid = c.userid
   and u.validto is null

Solution 2

The simple Way to copy the content from one table to other is as follow:

UPDATE table2 
SET table2.col1 = table1.col1, 
table2.col2 = table1.col2,
...
FROM table1, table2 
WHERE table1.memberid = table2.memberid

Solution 3

Try this:

update a

 set a.HolidayCity = b.CityID
FROM         Users1 AS a INNER JOIN
                      Users2 AS b ON a.UserId = b.UserId
WHERE     (a.ValidTo IS NULL)

Solution 4

UPDATE      U1
SET         U1.HolidayCity  =   U2.CityID
FROM        Users1          AS  U1
INNER JOIN  Users2          AS  U2  ON  U2.UserId = U1.UserId
WHERE       U1.ValidTo  IS NULL

If you have any questions about the above code, happy to expand, but this is a simple update format.

View more solutions

Comments

[BohdanZPM]

Thank you all for quick answers. They are all practically the same and they work for me.

[Keith]

I'd advise you to use ANSI joins, the code you have there is pretty outdated. Have a read here: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/…

[belhadj abdou]

@Leonidas199x yes i check tankyou for advise