Update Tables with Data from Other Tables with Local SQL

Learn About a SQL Update Statement

The UPDATE sentence of Local SQL (the SQL used by the BDE) doesn't 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 |


Assuming that we wanted to update the fields ShipToAddr1 and ShipToAddr2 of the Orders.db table with the values of the fields Addr1 and Addr2 respectively from the table Customer.db, for those records of Orders that have both fields blank, and joining the tables by the field CustNo present in both tables, perhaps we would be tempted to write:


    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, and we have to use subqueries to achieve the expected result:


  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 the subqueries.    


Share this article!

Follow us!

Find more helpful articles: