I need to update this table in SQL Server with data from its 'parent' table, see below:
Table: sale
id (int)
udid (int)
assid (int)
Table: ud
id (int)
assid (int)
sale.assid
contains the correct value to update ud.assid
.
What query will do this? I'm thinking of a join
but I'm not sure if it's possible.
Best Answer
Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support
MERGE
):ANSI/ISO:
MySQL:
SQL Server:
PostgreSQL:
Note that the target table must not be repeated in the
FROM
clause for Postgres. Main question: How to do an update + join in PostgreSQL?Oracle:
SQLite:
SQLite 3.33 added support for an
UPDATE
+FROM
syntax analogous to the PostgreSQL one:Main question: Update with Join in SQLite