Hi everyone,
I'm using the Retool PostgreSQL database. How do I mass update multiple rows from a table where I select several of them? Can't seem to get this to work.
I have a table called table11 and if I select 3 rows for example, I see that "SelectedRowKeys" shows all 3 like:
0 "9a7be3ad-ae74-4518-9214-5fe8424f3644"
1 "8f1ee9ea-76fe-48c1-8928-4f00c935b4d7"
2 "8ac0ca46-35b5-409d-8921-a7f93281c374"
Where those values are from the "id" Primary Key column.
If I write a manual SQL query, like this, it works:
UPDATE presentation_ctns
SET cur_msf = 50.00
WHERE id IN ('9a7be3ad-ae74-4518-9214-5fe8424f3644', '8f1ee9ea-76fe-48c1-8928-4f00c935b4d7', '8ac0ca46-35b5-409d-8921-a7f93281c374')
But how do I do it properly using the selected values? I tried this:
UPDATE presentation_ctns
SET cur_msf = 50.00
WHERE id IN ({{table11.selectedRowKeys}})
But that fails saying:
- message:"invalid input syntax for type uuid: "{"9a7be3ad-ae74-4518-9214-5fe8424f3644","8f1ee9ea-76fe-48c1-8928-4f00c935b4d7","8ac0ca46-35b5-409d-8921-a7f93281c374"}""
Not really sure how to put it in the proper format or what the issue is exactly.
ChatGPT says: The issue you're facing is due to the way Retool is formatting the array of UUIDs. Your database expects UUIDs as individual strings, but Retool is passing them as a single string that contains an array. You need to process {{table11.selectedRowKeys}} so that it gives the UUIDs as individual strings.
But I've been at it for 2 hours, we can't seem to figure it out or get anything to work as every query it suggests doesn't work.
Hmm trying it but it doesn't seem to work. Are you sure that's not just how to bulk update, as in if my query filters accounts where city = "Montreal" and therefore multiple rows are returned, it updates those? I'm trying to mass update multiple rows that are selected in the table but it doesn't work.
In the doc, they update a single record with {{table1.selectedRow.data.id}}. In my case, my table is table11, but there's no such parameter {{table11.selectedRow.data it gives an error in red, but I can do {{table11.selectedRow.id}} which returns a single value being the PrimaryKey of the currently selected row in the table, but that only updates ONE record.
When you check off multiple rows in a table, it seems to save the IDs of the selections to:
selectedRowKeys (Array)
selectedRows (Array)
selectedSourceRows (Array)
But none of these work, I can't select the ID value from them if I put for example {{table11.selectedRowKeys then it only shows the selected rows. None of these let me put .data after them:
If I just leave it like this as an example:
It fails running saying:
- statusCode:422
- error:"Unprocessable Entity"
- message:"select count(*) as "count" from "presentation_ctns" where "id" = $1 - invalid input syntax for type uuid: "{"{"id":"e1e233cb-9ad0-4ac9-b9a2-f0ad6ffe62a2"
Your SQL IN syntax is incorrect for parameterized queries.
Your syntax (not intuitive I know!) is this for SQL Server:
id in (SELECT value FROM STRING_SPLIT({{arrayOfIDs}}, ','))
I assume your arrayOfIDs would be table11.selectedRowKeys
What this does is pass the entire array to the server as a string, make it unspool it into separate values which will be evaluated by IN.
For others reference: If you are using Postgres (or Retool DB) this is the syntax:
where id in (SELECT unnest(string_to_array({{arrayOfIDs}}, ',')))
If you are using a JSON SQL query it is:
id in @({{arrayOfIDs}})
1 Like
Thank you that worked! I'm using PostgreSQL and it kept failing but this worked:
UPDATE presentation_ctns
SET cur_msf = 50.00
WHERE id IN (
SELECT unnest(string_to_array('{{table11.selectedRowKeys}}', ','))::uuid
)
Excellent!
And I missed that you were using UUIDs - I first discovered that particular gotcha with UUID fields just a few week a ago.
