In SQL Server, it is possible to do 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
It can be done in the following way. Notice that the query matches the two tables by the key field that they both have in common.
In the event that 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 a processUPDATE
first does aDELETE
and then aINSERT
You could do something like the following: