SQL UPDATE SELECT

update: 2026-02-06

Updating a table with values from another table using correlated subquery

SQL code valid for most common database systems and has been tested valid with
Paradox, dBase, SQL Server, MySQL, PostgreSQL, SQLite, InterBase, Firebird, Absolute Database, SQLMemTable (Paradox and dBase using BDE 5.2.0.2).
Code examples do not work with MS Access.
MySQL: see example of alternative SQL at page bottom.
SQL Server may also perform this task in another way also applicable to MySQL without using a correlated subquery
- please refer to SQL UPDATE from other table without ....

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:

START TRANSACTION; -- Unsupported in Paradox

UPDATE TableA
  SET ValueFieldA =
   (SELECT b.ValueFieldB
    FROM TableB b
    WHERE IDFieldB = TableA.IDFieldA
   )
-- Prevents NULL'ing fields for non-matching records:
WHERE IDFieldA IN
  (SELECT IDFieldB
   FROM TableB
   WHERE IDFieldB = TableA.IDFieldA
  )
;

The resulting updated TableA will be like this and avoiding update of the third record having no corresponding link value in the TableB:

The important step is to have both SELECT statements as correlated subqueries, that for each record will evaluate to the "outside" TableA.
Otherwise the result may end up NULL'ing the ValueField for the record without conditional match in TableB:

Equivalent results may be achieved using a correlated subquery like:

UPDATE TableA
  SET ValueFieldA =
   (SELECT TableB.ValueFieldB
    FROM TableB
    WHERE IDFieldB = TableA.IDFieldA
   )
-- Prevents NULL'ing fields for non-matching records:
WHERE EXISTS
  (SELECT IDFieldB
   FROM TableB
   WHERE TableA.IDFieldA = TableB.IDFieldB
  )
;

However, correlated subqueries needing to reevaluate for every record when propagating through the records of the destination table can be painfully slow on large datasets.

If you need to ad some extra criteria, this must be included in the second SELECT command in the latter half of the statement:

UPDATE TableA
  SET ValueFieldA =
   (SELECT TableB.ValueFieldB
    FROM TableB
    WHERE IDFieldB = TableA.IDFieldA
   )
-- Prevents NULL'ing fields for non-matching records:
WHERE IDFieldA IN
  (SELECT IDFieldB
   FROM TableB
   WHERE TableA.IDFieldA = TableB.IDFieldB
   AND TableB.ValueFieldB > 200
  )
;

giving this:

With MySQL the SQL alternatively may look like this:

UPDATE TableA, TableB
  SET ValueFieldA =
   (SELECT b.ValueFieldB
    FROM TableB b
    WHERE IDFieldB = TableA.IDFieldA
   )
-- Prevents NULL'ing fields for non-matching records:
WHERE TableA.IDFieldA = TableB.IDFieldB
;