SQL UPDATE from other table
Updating a table with values from another table without using correlated subquery
SQL code valid for e.g. SQL Server, MySQL, DBISAM (and possibly ElevateDB?) and others.
This method is NOT working with Paradox, dBase, Oracle, PostgreSQL, Absolute Database, SQLMemTable - please refer to UPDATE SELECT ..., also applicable to e.g. SQL Server.
The challenge updating a table based on related values from another table are:
1) updating only records with corresponding related values in source table
2) avoid NULL'ing records in destination table
not having related records in the source table.
As you will notice, only the IDField values 38 and 36 in TableA are present also in TableB.
So, the value for IDField value 25 should not update and should remain unaltered.
Sample tables, TableA and TableB:
The SQL to achieve the update of TableA with SQL Server and DBISAM:
UPDATE TableA |
The SQL to achieve the update of TableA with MySQL:
UPDATE TableA, TableB |
The resulting updated TableA will be like this and avoiding update of the third record having no corresponding link value in the TableB: