Categories
select sql sql-server sql-update tsql

How do I UPDATE from a SELECT in SQL Server?

4094

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement:

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

Is it also possible to update a table with SELECT? I have a temporary table containing the values and 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

1

  • What was the original intent of the question? Specific to Microsoft’s SQL Server (T-SQL)? Or a generic SQL question?

    Jan 30 at 1:40


5832

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

8

  • 25

    If you are editing the the link between tables (SET Table.other_table_id = @NewValue) then change the ON statement to something like ON Table.id = @IdToEdit AND other_table.id = @NewValue

    – Trisped

    Oct 24, 2012 at 18:41

  • 2

    @CharlesWood yeah. I have the same question in MySQL. It would be great if someone knows how to implement it to MySQL and share with everyone. I’m sure lots of people are looking for a MySQL version solution

    – Roger Ray

    Nov 27, 2013 at 3:34

  • 1

    How do I use an alias in set? update table set a.col1 = b.col2 from table a inner join table2 b on a.id = b.id; Instead I have to use update table set table.col1 = b.col2 from table a inner join table2 b on a.id = b.id;

    – ThinkCode

    Jan 20, 2014 at 23:08

  • 14

    Somewhat related, I often like to write my UPDATE queries as SELECT statements first so that I can see the data that will be updated before I execute. Sebastian covers a technique for this in a recent blog post: sqlity.net/en/2867/update-from-select

    Aug 21, 2015 at 19:48

  • 1

    You can’t do SET Table_A.col1 = SUM(Table_B.col1) or any other aggregate. Jamal’s answer allows you to put the aggregate in the SELECT stackoverflow.com/a/8963158/695671

    – Jason S

    Jul 14, 2019 at 22:31


853

In SQL Server 2008 (or newer), use MERGE

MERGE INTO YourTable T
   USING other_table S 
      ON T.id = S.id
         AND S.tsql="cool"
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

Alternatively:

MERGE INTO YourTable T
   USING (
          SELECT id, col1, col2 
            FROM other_table 
           WHERE tsql="cool"
         ) S
      ON T.id = S.id
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

10

  • 137

    MERGE can also be used for “Upserting” records; that is, UPDATE if matching record exists, INSERT new record if no match found

    – brichins

    May 15, 2012 at 19:51

  • 18

    This was around 10x quicker than the equivalent update…join statement for me.

    Apr 3, 2013 at 2:49

  • 18

    MERGE can also be used to DELETE. But be careful with MERGE as the TARGET table cannot be a remote table.

    – Möoz

    Aug 8, 2013 at 3:58

  • 26

    Merge bugs: mssqltips.com/sqlservertip/3074/…

    – Simon D

    Aug 27, 2014 at 9:38


  • 17

    @SimonD: pick any SQL Server keyword and you will find bugs. Your point? I wager there are more bugs (and more fundamental ones too) associated with UPDATE than MERGE, folks have just learned to live with them and they become part of the landscape (‘features’). Consider that blogs didn’t exist when UPDATE was the new kid on the block.

    Oct 3, 2014 at 15:29


800

UPDATE YourTable 
SET Col1 = OtherTable.Col1, 
    Col2 = OtherTable.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) AS OtherTable
WHERE 
    OtherTable.ID = YourTable.ID

3

  • 16

    This will tend to work across almost all DBMS which means learn once, execute everywhere. If that is more important to you than performance you might prefer this answer, especially if your update is a one off to correct some data.

    Feb 1, 2016 at 14:46

  • 3

    If you need to set the first table with aggregates from the second, you can put the aggregates in the select subquery, as you cannot do SET Table_A.col1 = SUM(Table_B.col1) (or any other aggregate function). So better than Robin Day’s answer for this purpose.

    – Jason S

    Jul 14, 2019 at 22:38

  • I really like this solution as it feels like a natural compliment to the way INSERT ... SELECT works. Thanks for sharing!

    Jun 6, 2021 at 17:50