In SQL Server, it is possible to make a 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 a 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
Literal translation of UPDATE from SELECT using SQL Server
It can be done in the following way. Notice that the query equals 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 those empty fields, and there may be data that already exists from the origin, it is advisable to use a query that verifies before doing the UPDATE , since a process of UPDATE first do 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))