Use of FOREACH to selectively update a data table

💬 App Building
1

This was suggested as a solution by Retool internal AI, but it still throws a syntax error "near FOREACH". Hopefully the query is pretty self-explanatory:

FOREACH variable IN ARRAY {{table1.selectedRowKeys}} LOOP

UPDATE
danexp
SET
data = {{textInput1.value}}
WHERE
id = variable;

END LOOP;

The use case is really simple. I have a table displaying the results of a query performed on danexp. I select multiple rows. I want the 'data' field for each of the selected rows/records to receive the value {{textInput1.value}}.

Thanks in advance...

2

Try using this instead:

3

Thanks for the suggestion. To use bulk update I will need to create an array that contains the picked id's and the updated value for the data field. Having large holes in my js, I am reluctant to go down that rabbit hole to construct the change array. Since my original solution was suggested by Retool AI, and is elegant, I would like to know how to get it to work.

4

Attempted the same thing using:

update danexp
set data = {{textInput1.value}}
where id in ({{table1.selectedRowKeys}});

which results in:

  • message:"column "id" does not exist"
5

And then this:

update danexp
set data = {{textInput1.value}}
where id in ({{table1.selectedRowKeys}});

gives this:

  • message:"invalid input syntax for type integer: "{"4","6"}""
6

Bard tells me that Postgres will not render array items as integers, so I have got to this, which does not throw any errors, but also does not update any values in the table.

image
image784×503 39.6 KB

7

SOLVED IT

update danexp
set data = {{textInput1.value}}
where did = any ({{table1.selectedRowKeys}});

3 Likes