How to UPDATE from a SELECT in SQL Server?

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
dbaever
Posts: 1
Joined: Tue Jul 16, 2019 3:44 pm

Tue Jul 16, 2019 3:52 pm

Hi, I know that it is possible to insert into a table using a SELECT statement in SQL Server, for example:

Code: Select all

INSERT INTO dbo.MyTable (col_N1, col_N2, col_N3)
SELECT MOT.col_N1,  MOT.col_N2,  MOT.col_N3 
FROM dbo.MyOtherTable AS MOT
WHERE MOT.col_N1 = 'True'
I was just wondering if it is also possible to UPDATE and table using a SELECT? I have a table containing some values, I need to UPDATE a different table using those values. Maybe something along the lines of:

Code: Select all

UPDATE dbo.MyTable SET col_N1, col_N2
SELECT col_N1, col_N2 
FROM dbo.MyOtherTable
WHERE MOT.col_N1 = 'True'
WHERE MyTable.ID = MyOtherTable.id
Is this possible at all?

Many thanks,
Bob547
Posts: 12
Joined: Sat Jun 22, 2019 7:05 pm

Tue Jul 16, 2019 4:02 pm

Hello @dbaever

The simplest way to achieve this is to INNER JOIN your two tables together, for example:

Code: Select all

UPDATE
	dbo.MyTable
SET
	MyTable.col_N1 = MyOtherTable.col_N1,
	MyTable.col_N2 = MyOtherTable.col_N2
FROM
	MyTable
	INNER JOIN MyOtherTable ON MyTable.ID = MyOtherTable.ID
WHERE
	MyOtherTable.col_N1 = 'True'
Post Reply

Social Media