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;    ENDEND

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.