Bulk update multiple rows in table

Currently, the data sources in ToolJet have operation for bulk update(GUI mode) but that only works for changes made in the single row. We will soon be adding a new operation for bulk updating the multiple rows but for now we can bulk update multiple rows by creating a Custom JS query.

In this guide, We have assumed that you have successfully connected the data source. For this guide, we will be using the PostgreSQL data source as an example database. Currently, this workaround can be used only for PostgreSQL and MySQL.

1. Create a query to get the data from the database

Create a postgresql query in SQL mode and enter

sql

SELECT * FROM tooljet // replace tooljet with your table name

Hit Run to fetch the data from the database

</div>

2. Display the data on the table

Go to the Components library on the right and drag a Table component onto the canvas
Click on the handle of the Table component to open its properties on the right sidebar
Populate the table with the data from the query by entering {{queries.<queryname>.data}} in the Data field

</div>

3. Make the columns editable

Under the Columns accordion, click on the column name that you want to make editable
On clicking the column name, a new section will open. Enable the toggle for Make editable to make the column editable

</div>

4. Enable Multiple Row Selection

Under the Row Selection accordion, enable the Allow Selection, Highlight Selected Row, and Bulk Selection option

</div>

5. Create a Custom JS query

Create a new Run Javascript query and use the code below to generate the SQL query for updating multiple rows.

const uniqueIdentifier = "id"
const cols = Object.values(components.table1.changeSet).map((col, index) => {
  return {
    col: Object.keys(col),
    [uniqueIdentifier]: Object.values(components.table1.dataUpdates)[index][uniqueIdentifier],
    values: Object.values(col),
  };
});

const sql = cols.map((column) => {
  const { col, id, values } = column;
  const cols = col.map((col, index) => `${col} = '${values[index]}'`);
  return `UPDATE users SET ${cols.join(", ")} WHERE id = '${id}';`;
});

return sql

:::info Here the Unique identifier is id, this is the column name that is used to identify the row in the database. Update the Unique identifier if you are using a different column name. Update table1 with the name of the table you are using. :::

</div>

6. Create an Update query

Create a postgresql query in SQL mode and rename it as update:

sql

{{queries.runjs1.data.join(' ')}}

This query will run the SQL query generated by the runjs1 query.

</div>

7. Adding event handlers to execute queries in sequence

Edit the Table component and add the event handler for Save Changes event so that whenever a user will edit the table and hit the Save Changes button the runjs1 query will run.
Add loading state to table so that whenever the users or update query is running the table will show a loading state.

{{queries.users.isLoading || queries.update.isLoading}} // add this in the loading state field of the table

</div>

Now, go to the runjs1 query and add a Event to run update query for Query Success Event. This will run the update query whenever the runjs1 query will be run.

</div>

Finally, go to the update query and add a Event to run the users query for Query Success Event. This will refresh the table whenever the update query will be run.