Updating multiple records at once in Retool DB with selectedRows

[Mick_Berkhout]

• Goal: In my Retool DB I have got a table called "tickets" with a column "broker" and a column "id". Based on the rows selected in a table (called selectTicketsToList) I want to run the query and update the broker_id to a specific value. I am getting stuck at getting the WHERE value for the ID correct.

• Steps: I tried all methods I can think of getting the ID in there (string, integer, quotes around it, no qoutes, squared brackets, no squaredebrackets , no join, a join). I also tried the GUI builder and AI

• Details:
  The query I am using is:

UPDATE
  ticket
SET
  broker_id = {{ listTicketBroker.selectedItem.id }}
WHERE
  id IN ({{ selectTicketsToList.selectedRows.map(item => item.id).join(', ') }})

[ZeroCodez]

@Mick_Berkhout You can remove the whitespace from .join(', ') and use .join(',') instead.

[Mick_Berkhout]

Hi ZeroCodez,

Thanks for your reply! That's also what I would have thought. I've modified the code to:

UPDATE
  ticket
SET
  broker_id = {{ listTicketBroker.selectedItem.id }}
WHERE
  id IN ({{ selectTicketsToList.selectedRows.map(item => item.id).join(',') }})

However, the error is still the same:

[ZeroCodez]

You can use the following JavaScript query:

// Get selected IDs from the table
const ids = selectTicketsToList.selectedRows.map(item => item.id);

// Construct the query with proper formatting
const query = `UPDATE ticket SET broker_id = {{ listTicketBroker.selectedItem.id }} WHERE id IN (${ids.join(',')});`;

// Return or use the query
return query;

Then, you can use the response from this JS query in your PostgreSQL query like this: {{ yourJSQuery.data }}.

[jg80]

For an array, the syntax is not IN ([array]) it is = ANY([array]). Try:

UPDATE
  ticket
SET
  broker_id = {{ listTicketBroker.selectedItem.id }}
WHERE
  id = ANY( {{ selectTicketsToList.selectedRows.map(item => item.id) }} ) 

You might have to fiddle with it a bit to return a simple array of ids, but hopefully you get the idea.