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

Queries and Resources
1

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..

UPDATE <SCHEMA.TABLE_NAME>
SET rpm_adjustment_monthly =
{{pricing_adjustment_monthly.recordUpdates.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!

2

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

3

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:

SELECT *
FROM myschema.mytable
WHERE ucid = {{pricing_parameters.selectedRow.data.ucid}} AND base_month = {{pricing_parameters.selectedRow.data.base_month}}
;

Hope that answers your question austin.

Thanks,
-Lou

4

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.

5

Works perfectly Austin. :grinning:
Much appreciated.
Thanks!

1 Like