How do I UPDATE from a SELECT in SQL Server?

The simple way to do it is:

UPDATE
table_to_update,
table_info
SET
table_to_update.col1 = table_info.col1,
table_to_update.col2 = table_info.col2

WHERE
table_to_update.ID = table_info.ID

If you use [MySQL][1] instead of SQL Server, the syntax is:

<!-- language: lang-sql -->

UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
Table1.col2 = Table2.col2

[1]:

[To see links please register here]

UPDATE TQ
SET TQ.IsProcessed = 1, TQ.TextName = 'bla bla bla'
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

To make sure you are updating what you want, select first

SELECT TQ.IsProcessed, 1 AS NewValue1, TQ.TextName, 'bla bla bla' AS NewValue2
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

Using alias:

UPDATE t
SET t.col1 = o.col1
FROM table1 AS t
INNER JOIN
table2 AS o
ON t.id = o.id

In the accepted answer, after the:

SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2

I would add:

OUTPUT deleted.*, inserted.*

What I usually do is putting everything in a roll backed transaction and using the `"OUTPUT"`: in this way I see everything that is about to happen. When I am happy with what I see, I change the `ROLLBACK` into `COMMIT`.

I usually need to document what I did, so I use the `"results to Text"` option when I run the roll-backed query and I save both the script and the result of the OUTPUT. (Of course this is not practical if I changed too many rows)

I'd modify [Robin's excellent answer][1] to the following:

UPDATE Table
SET Table.col1 = other_table.col1,
Table.col2 = other_table.col2
FROM
Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
other_table.col1 IS NOT NULL
AND Table.col1 IS NULL
)
OR (
other_table.col2 IS NOT NULL
AND Table.col2 IS NULL
)

Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.

[1]:

[To see links please register here]

Use:

drop table uno
drop table dos

create table uno
(
uid int,
col1 char(1),
col2 char(2)
)
create table dos
(
did int,
col1 char(1),
col2 char(2),
[sql] char(4)
)
insert into uno(uid) values (1)
insert into uno(uid) values (2)
insert into dos values (1,'a','b',null)
insert into dos values (2,'c','d','cool')

select * from uno
select * from dos

EITHER:

update uno set col1 = (select col1 from dos where uid = did and [sql]='cool'),
col2 = (select col2 from dos where uid = did and [sql]='cool')

OR:

update uno set col1=d.col1,col2=d.col2 from uno
inner join dos d on uid=did where [sql]='cool'

select * from uno
select * from dos

If the ID column name is the same in both tables then just put the table name before the table to be updated and use an alias for the selected table, i.e.:

update uno set col1 = (select col1 from dos d where uno.[id] = d.[id] and [sql]='cool'),
col2 = (select col2 from dos d where uno.[id] = d.[id] and [sql]='cool')

The below solution works for a MySQL database:

UPDATE table1 a , table2 b
SET a.columname = 'some value'
WHERE b.columnname IS NULL ;

Updating through `CTE` is more readable than the other answers here:

;WITH cte
AS (SELECT col1,col2,id
FROM other_table
WHERE sql = 'cool')
UPDATE A
SET A.col1 = B.col1,
A.col2 = B.col2
FROM table A
INNER JOIN cte B
ON A.id = B.id

The other way to update from a select statement:

UPDATE A
SET A.col = A.col,B.col1 = B.col1
FROM first_Table AS A
INNER JOIN second_Table AS B ON A.id = B.id WHERE A.col2 = 'cool'