UPDATE statement with two tables

Data Virtuality doesn't support referencing a second table in a JOIN clause, such as:

UPDATE "dwh.adventureworks_salesorderheader" s SET status = i.status FROM PostgreSQL.salesorderheader h WHERE h.salesorderid = s.salesorderid

To run these kinds of queries, there are two possible workarounds:

1. Copy both tables to the same data source and use a native call:
   

   BEGIN
       -- drop the temporary table in the target data source if already present
       DROP TABLE IF EXISTS dwh.tmp_salesorderheader;
   
       -- copy the data from the original data source (PostgreSQL) to the target data source (dwh)
       SELECT *
       INTO dwh.tmp_salesorderheader
       FROM PostgreSQL.salesorderheader;
   
       -- UPDATE using native 
       CALL dwh.native('UPDATE adventureworks_salesorderheader s SET status = h.status FROM tmp_salesorderheader h WHERE h.salesorderid = s.salesorderid');
   END;;

2. Iterate over each row.

   BEGIN
       -- iterate over each row from the source table
       LOOP ON (SELECT * FROM "PostgreSQL.salesorderheader") AS s
       BEGIN
           -- update the corresponding row in the target table
           UPDATE "dwh.adventureworks_salesorderheader" h
           SET h.status = s.status
           WHERE h.salesorderid = s.salesorderid;
       END
   END

The first option is usually prefered due to much better runtime, as iterating over each row, as shown in the second option, is a very time-consuming method.