Kbase 19402: How to Update a field in one table from another using SQL89.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/10/2000 |
|
In most SQL languages one can use the UPDATE statement in the expression clause of the SELECT statement to accomplish the above task. An example of this would be:
Update customer (customer.name) = (SELECT newCust.name FROM newCust
WHERE newCust.cust-num = customer.cust-num).
Progress SQL89 does not support using the SELECT statement in the expression clause of the UPDATE statement. To accomplish the desired results of the above code you must define and SQL CURSOR and loop through the results set returned by the SELECT statemnet. The following code is an example of how this can be done:
DEFINE VARIABLE vCnum AS INTEGER.
DEFINE VARIABLE vName AS CHARACTER.
DECLARE c1 CURSOR FOR
SELECT testing.c-num, testing.cname
FROM testing.
OPEN c1.
REPEAT:
FETCH c1 INTO vCnum, vName.
FOR EACH CUSTOMER:
UPDATE customer
SET customer.name = vName
WHERE customer.cust-num = vCnum.
END.
END.
CLOSE c1.
NOTE: If using the SQL89 from any application other than Progress, one must change the 4GL code to the equivalent of their application.