Mega Code Archive
Updating a table with data from another table with local sql
<![CDATA[
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?
Updating a table with data from another table with Local SQL
+-------------+ +-------------+
| 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 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
]]>