Update tables with data from other tables with Local SQL

Update tables with data from other tables with Local SQL

Learn about a SQL UPDATE statement.

The UPDATE statement in Local SQL (the SQL used by the BDE) does not support JOIN, so how do you update a table with data from another table?

Here's how:

+-------------+    +-------------+
| Orders.db   |    | Customer.db |
+-------------+    +-------------+
| CustNo      | <---------> | CustNo |
| ShipToAddr1 |               | Addr1  |
| ShipToAddr2 |               | Addr2  |
+-------------+    +-------------+

Assume we want to update the fields ShipToAddr1 and ShipToAddr2 in the Orders.db table with the values of the fields Addr1 and Addr2, respectively, from the Customer.db table. We want to do this only for Orders records that have both fields blank, joining the tables by the CustNo field present in both tables. We might be tempted to write:

UPDATE Orders INNER JOIN Customer
    ON Customer.CustNo = Orders.CustNo
    SET ShipToAddr1 = Addr1, ShipToAddr2 = Addr2
    WHERE ShipToAddr1 = '' AND ShipToAddr2 = ''

However, in Local SQL (the one used by the BDE), joins are not supported in the SQL UPDATE statement, so we have to use subqueries to achieve the expected result:

UPDATE Orders
    SET ShipToAddr1 = (SELECT Addr1 FROM Customer WHERE
                       Customer.CustNo = Orders.CustNo),
        ShipToAddr2 = (SELECT Addr2 FROM Customer WHERE
                       Customer.CustNo = Orders.CustNo)
    WHERE ShipToAddr1 = '' AND ShipToAddr2 = ''

In the SQL UPDATE statement topic of the Local SQL Guide, you can find an example of a 1-to-many relationship that uses grouping in the subqueries.