Consultor Eletrônico

Status: Verified

GOAL:

How to replicate data in SQL-92 between 2 existing tables in the same database.

GOAL:

How to update TableA.Field1 = TableB.Field1 where TableA.Field2 = TableB.Field2 .

GOAL:

Is the UPDATE statement supported in conjunction with INNER JOINs?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge Category: SQL
OpenEdge 10.0x
OpenEdge 10.1x

FIX:

The UPDATE statement does not support INNER JOIN syntax. For example, the following UPDATE statement will generate a syntax error:
UPDATE Pub.Customer INNER JOIN Pub.BillTo ON
Customer.CustNum=BillTo.BillToID SET Customer.Name=BillTo.Name;

A workaround is to use SQL-92 Database JAVA trigger. For example:

CREATE TRIGGER Replication AFTER UPDATE OF (name) ON pub.BillTo
REFERENCING OLDROW
FOR EACH ROW
IMPORT
import java.sql.* ;
BEGIN
Integer custnum = (Integer) OLDROW.getValue(1, INTEGER);
String name = (String) OLDROW.getValue(3, VARCHAR);
SQLIStatement update_stmt = new SQLIStatement ("UPDATE pub.customer SET name = ? WHERE custnum = ?");
update_stmt.setParam(1, name);
update_stmt.setParam(2, billtoid);
update_stmt.execute();
END;
COMMIT;

Then run a dummy UPDATE statement to activate the trigger:

UPDATE pub.billto
SET name = name
WHERE custnum < 10;
COMMIT;

The same results can be obtained by making your own Java application in combination with the JDBC driver.