“Hi Team, I am working on a workflow in Process Studio where I need to synchronize my stream data with an existing database table using the Update Plugin. Even though my connection is successful, the records aren’t reflecting the changes. Could someone explain the correct mapping for the ‘Lookup’ vs ‘Update’ fields? Specifically, if I have a primary key like ProductID, Thanks!”
In the Update step, the distinction is between locating the row versus modifying the row:
1. Lookup Fields (WHERE clause)
-
What it means: The criteria used to find the existing database row.
-
How to map: Map your primary key or unique identifier here.
-
Table field: The column name in the database (e.g.,
ProductID). -
Comparator:
= -
Stream field 1: The incoming stream field (e.g.,
ProductID).
-
2. Update Fields (SET clause)
-
What it means: The database columns you want to overwrite with incoming data once the record is found.
-
How to map: Map only the values that should change (e.g.,
Price,StockQuantity,Description).-
Table field: Target database column.
-
Stream field: Incoming stream field holding the new value.
-
Do not map
ProductIDhere unless you intend to update the ID itself.
-
Why Your Changes Might Not Reflect
-
No Match Found: If the incoming
ProductIDdoes not match an existing record, theUpdatestep silently skips the row (it does not insert new records).- Fix: If you need to add missing rows, switch to the
Insert / Updatestep.
- Fix: If you need to add missing rows, switch to the
-
Commit Size: Changes are batched. If your stream has fewer rows than the Commit size parameter (e.g., default
100), they will only commit once the stream completes. -
Transaction Rollback: If the workflow fails downstream or uncommitted transactions aren’t finalized by the database, the changes will roll back.
