Update a table with values from a related table

Question

I have two tables, Users and Company.

I want to transfer values from a Active column in the Users table to the Active column in the Company table, where the CompanyID in matches ID.

This is an example table. It has many thousands of rows, and there is 1 on 1 relationship between Company and Users:

Users:
CompanyID    Active
458          1
685          1
58           0

Company:
ID           Active
5            Null
3            Null
58           Null
685          Null

The final Company table should look something like this where the Null has been replaced with the value from the Users table.

Company:
ID           Active
5            Null
3            Null
58           0
685          1

Answer 1

You can simply perform an UPDATE that uses a JOIN between the two tables like so:

UPDATE c
SET Active = u.Active
FROM Company c
INNER JOIN Users u ON u.CompanyId = c.ID

Full working sample code:

CREATE TABLE #Users
    (
      CompanyId INT ,
      Active BIT
    )

INSERT  INTO #Users
        ( CompanyId, Active )
VALUES  ( 458, 1 ),
        ( 685, 1 ),
        ( 58, 0 )

CREATE TABLE #Company
    (
      ID INT ,
      Active BIT
    )

INSERT  INTO #Company
        ( ID, Active )
VALUES  ( 5, NULL ),
         ( 3, NULL ),
         ( 58, NULL ),
         ( 685, NULL )

UPDATE c
SET Active = u.Active
FROM #Company c
INNER JOIN #Users u ON u.CompanyId = c.ID

SELECT * FROM #Company

DROP TABLE #Users
DROP TABLE #Company 

You'll notice that the UPDATE statement in the sample code uses aliases c and u to reference the two tables.

Caveat:

As stated in the comments, this assumes that you only ever have a 1 to 1 relationship between Company and Users. If there is more than one user assigned to the same company, you will need to filter the Users to pick the one you want to use, otherwise you may get unexpected results.