SAPDS Update Datastore_1 table1 based on Datastore_2 table2 ?

[dunncrew]

I need to update some rows in a table in Datastore_1 based on values in a table in Datastore_2.

In MSSQL I would just specify the different databases, or even linked server names in the SQL code.

Is there a way to code an update (or Join) statement that spans different DataStores ?

One datastore connects to MSSQL. The other connects to IBMi.

Basically, something like

UPDATE Datastore_1.Table1
SET Status = 'E'
WHERE Rec#1 in (Select Rec#2 from Datastore_2.Table_2)

[denise_meyer]

you can read both sources and use the query transform to perform this. Or you can use lookup function in a query transform as well to lookup in the 2nd table.

Thanks,
Denise
SAP Support

[RichS]

to expand...once a query transform is developed with the data in the desired form for the target table, you can try applying the updates in one of two ways (that I know of): 1) send the output to a Map_Operation transform, map "normal" Input rows to "update" output and all other inputs to "discard", send results of the Map_Operation to the target table; or 2) send the query output directly to the target table and adjust the values on the "Options" tab values on the target table to allow a merge - Auto correct load would be Yes and Allow merge or upsert would be Yes.