How can we UPDATE from a SELECT in an SQL Server

Question

In an SQL Server, we can insert rows into a table with the help of INSERT.. SELECT command. The statement is shown below :

INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3 
FROM other_table 
WHERE sql = 'cool'

Can we also update a table with SELECT? Having a temporary table containing the values and also would like to update another table using those values. Perhaps something like this:

UPDATE Table SET col1, col2
SELECT col1, col2 
FROM other_table 
WHERE sql = 'cool'
WHERE Table.id = other_table.id

Answer 1

We can firstly use SELECT statement to fetch the reference column and target column values.

SELECT e.City,A.City,e.PostCode,A.PostCode
FROM Employee e
INNER JOIN
[Address] add
ON e.EmpID = A.EmpID

Then, you will perform slight changes in your query, and it will prepare an UPDATE statement as shown below.

1. Replace the select keyword with update.
2. Specify the table name or alias name that needs to be updated.
3. Use a set keyword and equals symbol (=) between referencing and target columns. 

UPDATE u set
u.City=A.City,
e.PostalCode=A.PostalCode
FROM Employee e
INNER JOIN [Address] a
ON e.EmpID = A.EmpID