recordUpdates array object for multi-row table updates, or inserts not working

Retool application requirement is to develop a redshift based CRUD app based on manipulation of a single retool table object.

Retool table object updates will be used to apply bulk insert or updates to a single redshift table - typically involving no more than 12 rows.

Redshift does not enforce PK uniqueness and therefore SQL based bulk update was opted over GUI based retool approach.

PROBLEM: recordUpdates['0'] works fine, but usage/reference of entire array needed to apply a multi row insert or update of Redshift Table returns no errors messages from Redshift and ZERO table updates. i.e. use of recordUpdates instead of recordUpdates['N'] returns no success or failed response and zero changes to table.

Here's actual statement used as table "Bulk Update Action" which contains zero syntax errors..

SET rpm_adjustment_monthly =

views_adjustment_monthly = {{pricing_adjustment_monthly.recordUpdates.views_adjustment_monthly}}

WHERE ucid = {{pricing_adjustment_monthly.recordUpdates.ucid}}
AND base_month = {{pricing_adjustment_monthly.recordUpdates.base_month}}
AND view_month = {{pricing_adjustment_monthly.recordUpdates.view_month}}

Any assistance would be greatly appreciated.

Thank you!

Hey there! Mind sharing what the shape of the data you put into “Array of records to insert/update”?

Source Redshift DB table structure is:
MyTable (
base_month - date <-- retool table column type is default
view_month - date <-- retool table column type is default
rpm_adjustment_monthly - numeric <-- retool table column type is float
views_adjustment_monthly - numeric <-- retool table column type is float

Query used to populate retool table is:

FROM myschema.mytable
WHERE ucid = {{}} AND base_month = {{}}

Hope that answers your question austin.


Glad we able to sort this out over video, but for posterity’s sake I’ll leave an answer here as well:

In Retool, we don’t yet support bulk updates via a composite key (a combination of primary keys that is unique). As a workaround, you can create a “Run JS code” query that loops through each of the values in table.recordUpdates and trigger a query that updates a single row.

Works perfectly Austin. :grinning:
Much appreciated.

1 Like