Updating a table with data from another table with Local SQL

Problem/Question/Abstract:

The UPDATE sentence of Local SQL (the SQL used by the BDE) doesn't support JOIN... How two update a table with data from another table?

Answer:

Orders.db

Customer.db
CustNo
ShipToAddr1
ShipToAddr2
<<--------->
CustNo
Addr1
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 temped 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 UPDATE statement, and 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 "UPDATE statement" topic of the Local SQL Guide you can find an example of a 1-to-many relationship that uses grouping the subqueries.


Copyright (c) 2001 Ernesto De Spirito
Visit: http://www.latiumsoftware.com/delphi-newsletter.php