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?

Thank you all for quick answers. They are all practically the same and they work for me. — BohdanZPM
– BohdanZPM, Commented Aug 18, 2017 at 13:34

SqlZim · Accepted Answer · 2017-08-18 13:13:31Z

15

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

answered Aug 18, 2017 at 13:13

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

Comments

belhadj abdou · Accepted Answer · 2017-08-18 13:14:53Z

3

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

answered Aug 18, 2017 at 13:14

belhadj abdou

1784 silver badges16 bronze badges

2 Comments

Keith Over a year ago

I'd advise you to use ANSI joins, the code you have there is pretty outdated. Have a read here: sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

belhadj abdou Over a year ago

@Leonidas199x yes i check tankyou for advise

Miriam Farber · Accepted Answer · 2017-08-18 18:34:06Z

2

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)

edited Aug 18, 2017 at 18:34

Miriam Farber

19.7k15 gold badges65 silver badges78 bronze badges

answered Aug 18, 2017 at 13:14

Whencesoever

2,30617 silver badges26 bronze badges

Comments

Keith · Accepted Answer · 2017-08-18 13:13:14Z

1

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.

answered Aug 18, 2017 at 13:13

Keith

1,03611 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related