Organizational Research By

Surprising Reserch Topic

UPDATE from SELECT using SQL Server


n SQL Server, it's possible to insert into a table using a SELECT statement:

INSERT INTO Table (col, col2, col3)
    SELECT col, col2, col3 FROM other_table WHERE sql = 'cool'

Is it also possible to update via a 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

 


asked May 22, 2015 in MYSQL by rahulgii
0 votes
44 views



Related Hot Questions

3 Answers

0 votes
 
Best answer
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
answered May 22, 2015 by rahulgii
0 votes

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;
answered May 22, 2015 by rahulgii
0 votes

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.

answered May 22, 2015 by rahulgii

...