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

User · Answer

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

User · Answer

If you use MySQL instead of SQL Server  the syntax is   UPDATE Table1 INNER JOIN Table2 ON Table1 id   Table2 id SET Table1 col1   Table2 col1      Table1 col2   Table2 col2

User · Answer

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

User · Answer

And if you wanted to join the table with itself  which won t happen too often    update t1                    -- just reference table alias here set t1 somevalue   t2 somevalue from table1 t1               -- these rows will be the targets inner join table1 t2         -- these rows will be used as source on                           -- the join clause is whatever suits you

User · Answer

UPDATE table1 SET column1    SELECT expression1                FROM table2                WHERE conditions   WHERE conditions     The syntax for the UPDATE statement when updating one table with data from another table in SQL Server

User · Answer

I was using INSERT SELECT Before  for those who want to use new stuff i will put this solution that works similar but much shorter  UPDATE table1                                       table that s going to be updated LEFT JOIN                                           type of join table2 AS tb2                                       second table and rename for easy ON tb2 filedToMatchTables   table1 fieldToMatchTables  fileds to connect both tables SET    fieldFromTable1   tb2 fieldFromTable2               field to be updated on table1  field1FromTable1   tb2 field1FromTable2             This is in the case you need to field1FromTable1   tb2 field1FromTable2             update more than one field field1FromTable1   tb2 field1FromTable2             remember to put   at the end

User · Answer

Consolidating all the different approaches here    Select update Update with a common table expression Merge   Sample table structure is below and will update from Product BAK to Product table   Product  CREATE TABLE  dbo   Product        Id   int  IDENTITY 1  1  NOT NULL       Name   nvarchar  100  NOT NULL       Description   nvarchar  100  NULL   ON  PRIMARY    Product BAK      CREATE TABLE  dbo   Product BAK            Id   int  IDENTITY 1  1  NOT NULL           Name   nvarchar  100  NOT NULL           Description   nvarchar  100  NULL       ON  PRIMARY    1  Select update      update P1     set Name   P2 Name     from Product P1     inner join Product Bak P2 on p1 id   P2 id     where p1 id   2   2  Update with a common table expression        With CTE as               select id  name from Product Bak where id   2           update P     set Name   P2 name     from  product P  inner join CTE P2 on P id   P2 id     where P2 id   2   3  Merge      Merge into product P1     using Product Bak P2 on P1 id   P2 id      when matched then     update set p1  description    p2  description   p1 name   P2 Name    In this Merge statement  we can do inset if not finding a matching record in the target  but exist in the source and please find syntax       Merge into product P1     using Product Bak P2 on P1 id   P2 id       when matched then     update set p1  description    p2  description   p1 name   P2 Name       WHEN NOT MATCHED THEN     insert  name  description      values p2 name  P2 description

User · Answer

For the record  and others searching like I was   you can do it in MySQL like this   UPDATE first table  second table SET first table color   second table color WHERE first table id   second table foreign id

User · Answer

There is even a shorter method and it might be surprising for you   Sample data set   CREATE TABLE  SOURCE   ID  INT   Desc  VARCHAR 10    CREATE TABLE  DEST     ID  INT   Desc  VARCHAR 10     INSERT INTO  SOURCE VALUES 1  Desc 1     2   Desc 2     3   Desc 3    INSERT INTO  DEST   VALUES 1  Desc 4     2   Desc 5     3   Desc 6      Code       UPDATE  DEST SET  DEST  Desc     SOURCE  Desc  FROM  SOURCE WHERE  DEST  ID     SOURCE  ID

User · Answer

The same solution can be written in a slightly different way as I would like to set the columns only once I have written about both the tables  Working in mysql  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   UPDATE Table t    SELECT col1  col2 FROM other table WHERE sql    cool    o SET t col1   o col1  t col2 o col2 WHERE t id   o id

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In SQL Server 2008  or better   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

User · Answer

If you are using SQL Server you can update one table from another without specifying a join and simply link the two from the where clause  This makes a much simpler SQL query   UPDATE Table1 SET Table1 col1   Table2 col1      Table1 col2   Table2 col2 FROM     Table2 WHERE     Table1 id   Table2 id

User · Answer

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

User · Answer

like this  but you must sure update table and table after from have be same   UPDATE Table SET col1  col2 FROM table inner join other table Table id   other table id WHERE sql    cool

User · Answer

declare  tblStudent table  id int name varchar 300   declare  tblMarks table  std id int std name varchar 300  subject varchar 50  marks int   insert into  tblStudent Values  1  Abdul   insert into  tblStudent Values 2  Rahim    insert into  tblMarks Values 1     Math  50  insert into  tblMarks Values 1     History  40  insert into  tblMarks Values 2     Math  30  insert into  tblMarks Values 2     history  80    select   from  tblMarks  update m set m std name s name  from  tblMarks as m left join  tblStudent as s on s id m std id  select   from  tblMarks

User · Answer

Another possibility not mentioned yet is to just chuck the SELECT statement itself into a CTE and then update the CTE    WITH CTE      AS  SELECT T1 Col1                  T2 Col1 AS  Col1                  T1 Col2                  T2 Col2 AS  Col2          FROM   T1                 JOIN T2                   ON T1 id   T2 id            Where clause added to exclude rows that are the same in both tables            Handles NULL values correctly            WHERE EXISTS SELECT T1 Col1                               T1 Col2                        EXCEPT                        SELECT T2 Col1                                T2 Col2   UPDATE CTE SET    Col1    Col1         Col2    Col2   This has the benefit that it is easy to run the SELECT statement on its own first to sanity check the results  but it does requires you to alias the columns as above if they are named the same in source and target tables   This also has the same limitation as the proprietary UPDATE     FROM syntax shown in four of the other answers  If the source table is on the many side of a one-to-many join then it is undeterministic which of the possible matching joined records will be used in the Update  an issue that MERGE avoids by raising an error if there is an attempt to update the same row more than once

User · Answer

This may be a niche reason to perform an update  for example  mainly used in a procedure   or may be obvious to others  but it should also be stated that you can perform an update-select statement without using join  in case the tables you re updating between have no common field    update     Table set     Table example   a value from     TableExample a where     Table field    key value  -- finds the row in Table      AND a field    key value  -- finds the row in TableExample a

User · Answer

UPDATE from SELECT with INNER JOIN in SQL Database  Since there are too many replies of this post  which are most heavily up-voted  I thought I would provide my suggestion here too  Although the question is very interesting  I have seen in many forum sites and made a solution using INNER JOIN with screenshots   At first  I have created a table named with schoolold and inserted few records with respect to their column names and execute it   Then I executed SELECT command to view inserted records     Then I created a new table named with schoolnew and similarly executed above actions on it     Then  to view inserted records in it  I execute SELECT command     Now  Here I want to make some changes in third and fourth row  to complete this action  I execute UPDATE command with INNER JOIN     To view the changes I execute the SELECT command     You can see how Third and Fourth records of table schoolold easily replaced with table schoolnew by using INNER JOIN with UPDATE statement

User · Answer

I add this only so you can see a quick way to write it so that you can check what will be updated before doing the update   UPDATE Table  SET  Table col1   other table col1       Table col2   other table col2  --select Table col1  other table col Table col2 other table col2       FROM     Table  INNER JOIN     other table      ON     Table id   other table id

User · Answer

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

User · Answer

The other way is to use a derived table   UPDATE t SET t col1   a col1      t col2   a col2 FROM   SELECT id  col1  col2 FROM  tbl2  a INNER JOIN  tbl1 t ON t id   a id     Sample data  DECLARE  tbl1 TABLE  id INT  col1 VARCHAR 10   col2 VARCHAR 10   DECLARE  tbl2 TABLE  id INT  col1 VARCHAR 10   col2 VARCHAR 10    INSERT  tbl1 SELECT 1   a    b  UNION SELECT 2   b    c   INSERT  tbl2 SELECT 1   1    2  UNION SELECT 2   3    4   UPDATE t SET t col1   a col1      t col2   a col2 FROM   SELECT id  col1  col2 FROM  tbl2  a INNER JOIN  tbl1 t ON t id   a id  SELECT   FROM  tbl1 SELECT   FROM  tbl2

User · Answer

Important to point out  as others have  MySQL or MariaDB use a different syntax  Also it supports a very convenient USING syntax  in contrast to T SQL   Also INNER JOIN is synonymous with JOIN  Therefor the query in the original question would be best implemented in MySQL thusly  UPDATE     Some Table AS Table A  JOIN     Other Table AS Table B USING id   SET     Table A col1   Table B col1      Table A col2   Table B col2  WHERE     Table A col3    cool   I ve not seen the a solution to the asked question in the other answers  hence my two cents   tested on PHP 7 4 0 MariaDB 10 4 10

User · Answer

The following example uses a derived table  a SELECT statement after the FROM clause  to return the old and new values for further updates   UPDATE x SET    x col1   x newCol1         x col2   x newCol2 FROM    SELECT t col1                 t2 col1 AS newCol1                 t col2                 t2 col2 AS newCol2         FROM    table  t                JOIN other table t2                  ON t ID   t2 ID  x

User · Answer

You can use from this for update in sql server  UPDATE     T1 SET    T1 col1   T2 col1     T1 col2   T2 col2 FROM    Table1 AS T1 INNER JOIN Table2 AS T2     ON T1 id   T2 id WHERE     T1 col3    cool

User · Answer

Here is another useful syntax   UPDATE suppliers SET supplier name    SELECT customers name                      FROM customers                      WHERE customers customer id   suppliers supplier id  WHERE EXISTS  SELECT customers name               FROM customers               WHERE customers customer id   suppliers supplier id     It checks if it is null or not by using  WHERE EXIST

User · Answer

Using alias   UPDATE t    SET t col1   o col1   FROM table1 AS t          INNER JOIN         table2 AS o           ON t id   o id

User · Answer

UPDATE table AS a INNER JOIN table2 AS b ON a col1   b col1 INNER JOIN     AS     ON           SET     WHERE

User · Answer

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

User · Answer

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

User · Answer

UPDATE YourTable  SET Col1   OtherTable Col1       Col2   OtherTable Col2  FROM       SELECT ID  Col1  Col2      FROM other table  AS OtherTable WHERE      OtherTable ID   YourTable ID

User · Answer

Option 1  Using Inner Join   UPDATE     A SET     A col1   B col1      A col2   B col2 FROM     Some Table AS A     INNER JOIN Other Table AS B         ON A id   B id WHERE     A col3    cool    Option 2  Co related Sub query  UPDATE table  SET Col1   B Col1       Col2   B Col2  FROM       SELECT ID  Col1  Col2      FROM other table  B WHERE      B ID   table ID

User · Answer

The below solution works for a MySQL database   UPDATE table1 a   table2 b  SET a columname    some value   WHERE b columnname IS NULL

[sql] How do I UPDATE from a SELECT in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to select