How do I UPDATE from a SELECT in SQL Server?

Question

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

Solution 1

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'

Solution 2

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;

Solution 3

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

Solution 4

I'd modify Robin's excellent answer 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.

Solution 5

One way

UPDATE t 
SET t.col1 = o.col1, 
    t.col2 = o.col2
FROM 
    other_table o 
  JOIN 
    t ON t.id = o.id
WHERE 
    o.sql = 'cool'

View more solutions

Comments

[Ali NajafZadeh]

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'

[Ali NajafZadeh]

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

[Peter Mortensen]

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

[Martin Smith]

This assumes none of the columns are nullable though.

[quillbreaker]

You're right, I was typing the example by hand. I've added a third and fourth clause to the where statement to deal with that.

[brichins]

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

[Martin Smith]

WHERE EXISTS(SELECT T1.Col1, T1.Col2 EXCEPT SELECT T2.Col1, T2.Col2)) is more concise.

[Raptor]

is there any meaning of the name CTE ?

[Martin Smith]

@ShivanRaptor - It is the acronym for Common Table Expression. Just an arbitrary alias in this case.

[Trisped]

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

[Paul Suart]

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

[quillbreaker]

Martin - it took me a while to get the knack of what that statement does. "It's a select in a where clause but it's not a table subquery?" was a proposition I was having difficulty wrapping my brain around. Now that I've got it, I've come to learn how valuable a technique it is, especially for some kinds of Data Warehousing operations.

[dburges]

This is not SQl Server syntax and it will not work in SQL server

[Barka]

shouldn't the statement also contain these two in the where clause? (other_table.col1 is null and table.col1 is not null) or (other_table.col2 is null and table.col2 is not null)

[quillbreaker]

Depends on if you want to replace nulls in the destination with nulls from the source. Frequently, I don't. But if you do, Martin's construction of the where clause is the best thing to use.

[Möoz]

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

[VeeTheSecond]

This also works well with multiple CTEs: ;WITH SomeCompexCTE AS (...), CTEAsAbove AS (SELECT T1.Col1,... FROM T1 JOIN SomeComplexCTE...) UPDATE CTEAsAbove SET Col1=_Col1, ...

[Roger Ray]

@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

[ThinkCode]

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;

[Tracker1]

Thanks for this, hadn't seen MERGE definitely like the syntax, and that you can use aliases (which don't work in the update/set/from) much better... I've been using WITH statements for the query part.

[dburges]

If I can't guarantee teh results of a merge, and I can guarnatee the results of doing separate insert and update statments, then it is a bad idea to use merge.

[onedaywhen]

@HLGEM: ...I assume you are aware of the case where the result of an UPDATE..FROM is not guaranteed? (hint: many side of a one-to-many join where the result is arbitrary) Is that the kind of 'bug' you are alluding to?

[Mark Hurd]

+1 but you should have used relevant alias names like targett1 and sourcet1 rather than (or as well as) comments.

[Simon D]

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

[onedaywhen]

@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.

[Endrju]

@SimonD I'm sure you would be able to find problems similar to these MERGE "bugs" (well...) in separate INSERT/UPDATE/DELETE combo. One thing - always use MERGE in SERIALIZABLE transaction (or use HOLDLOCK hint) if you want to avoid most common race conditions. Same for manual merge using INSERT/UPDATE/DELETE...

[dennislloydjr]

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

[Alan Macdonald]

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.

[lmo]

This answer turned up in the low quality review queue, presumably because you don't provide any explanation of the code. If this code answers the question, consider adding adding some text explaining the code in your answer. This way, you are far more likely to get more upvotes — and help the questioner learn something new.

[Bartosz X]

YES - there is no JOIN on purpose and NO - this can't be applied on table variables.

[Jenna Leaf]

I think if you use [_id] on your #SOURCE not [ID] the same as #DESTINATION's, they might let you do JOIN. "on #DESTINATION.ID=#SOURCE._id. Or even use table variable like @tbl, "on PermTable.ID=@memorytbl._id". Have you tried? I am using a phone to reply this, no computer to try.

[Martin Smith]

What does this have to do with updating from a SELECT?

[Bartosz X]

This is the same idea but another method - you don't have to put "select" at all to achieve JOIN and WHERE in update statement - which is SELECT type of query without even writing SELECT

[unbob]

Portability note: MERGE is ANSI SQL; UPDATE...FROM is not. Having used both, I find MERGE semantics more intelligible - I'm less likely to mess up doing a MERGE than an UPDATE...FROM. YMMV.

[Jason S]

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]

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.

[Denziloe]

Is there any reason UPDATE FROM statements are frequently written with aliasing? As far as I can tell it's unnecessary. You could remove the aliases and replace Table_A with Some_Table and Table_B with Other_Table throughout that example... no need to give things different names.

[bladekp]

For MySQL db: UPDATE Table_A, Table_B SET Table_A.col1 = Table_B.col1 WHERE Table_A.id = Table_B.table_a_id

[Pratyush Raizada]

Did it work for you? I used exact same query but had errors when used inner join, alias coun't be resolved. However the co-related sub query worked perfectly.

[Pratyush Raizada]

I don't have the exact error logs but the alias A was being referenced before the assignment, which caused the error.

[pat capozzi]

I used the correlated sub query

[Saad Abbasi]

What if we want to update Table2.col1? how will we do that. table two is extracted on the base of the query condition.

[Adam]

This does not work for mysql. For mysql you need to make a join

[Anthony Iacono]

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

[theking2]

This syntax is not correct. Could you edit your answer to show us what you mean?

[Panzercrisis]

I feel this should be the accepted answer, because it keeps things simple and to the point.

[Peter Mortensen]

The first line is incomprehensible. Can you fix it?

[Peter Mortensen]

Re "the query in the original question": What? The very first revision was tagged with SQL Server (the Microsoft product).

[Peter Mortensen]

This is partly incomprehensible. Can you fix it?

[Peter Mortensen]

What was this tested on? SQL Server? MySQL? Something else? What version?

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? What was it tested on? What version of SQL Server? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

What do you mean by "use from this for update" (seems incomprehesible)? Are "from" and/or "update" literal (SQL) or not? Can you fix it? (But without "Edit:", "Update:", or similar - the question/answer should appear as if it was written today.)

[Peter Mortensen]

What do you mean by "and please find syntax"? Can you elaborate?

[Bludzee]

The only answer that worked for me. I'm using MariaDB, and my query is of the form: SELECT value FROM table_1 INNER JOIN ( SELECT * FROM table_2 WHERE ..... ) ON ( ...... )