UPDATE from a SELECT using SQL Server

Question:

In SQL Server, it is possible to do an INSERT INTO to a table using a SELECT :

INSERT INTO tabla (col, col2, col3)
    SELECT col, col2, col3 FROM otra_tabla WHERE sql = 'ok'

Is this also possible for an UPDATE ? I have a temporary table that contains the values, and I would like to update another table using those values. Maybe something like:

UPDATE tabla SET col1, col2
    SELECT col1, col2 FROM otra_tabla WHERE sql = 'ok'
    WHERE tabla.id = otra_tabla.id

Answer:

Literal translation of UPDATE from SELECT using SQL Server

It can be done in the following way. Notice that the query matches the two tables by the key field that the two have in common.

UPDATE
    Tabla_A
SET
    Tabla_A.col1 = Tabla_B.col1,
    Tabla_A.col2 = Tabla_B.col2
FROM
    alguna_tabla AS Tabla_A
    INNER JOIN otra_tabla AS Tabla_B
        ON Tabla_A.id = Tabla_B.id
WHERE
    Tabla_A.col3 = 'ok'

In the case where the destination table does not have these empty fields, and there may be data that already exists from the source, it is convenient to use a query that verifies before doing the UPDATE , since an UPDATE process first performs a DELETE and then a INSERT

You could do something like the following:

UPDATE
     Tabla
SET
     Tabla.col1 = otra_tabla.col1,
     Tabla.col2 = otra_tabla.col2 
FROM
     Tabla
INNER JOIN     
     otra_tabla
ON     
     Tabla.id = otra_tabla.id 
WHERE EXISTS(SELECT Tabla.Col1, Tabla.Col2 EXCEPT SELECT otra_tabla.Col1, otra_tabla.Col2))
Scroll to Top