Skip to main content
Version: 2.19.0

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
SELECT * FROM tooljet // replace tooljet with your table name
  • Hit Run to fetch the data from the database

ToolJet - How To - Bulk update multiple rows in table

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

ToolJet - How To - Bulk update multiple rows in table

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

ToolJet - How To - Bulk update multiple rows in table

4. Enable Multiple Row Selection​

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

ToolJet - How To - Bulk update multiple rows in table

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.

ToolJet - How To - Bulk update multiple rows in table

6. Create an Update query​

  • Create a postgresql query in SQL mode and rename it as update:
{{queries.runjs1.data.join(' ')}}
  • This query will run the SQL query generated by the runjs1 query.

ToolJet - How To - Bulk update multiple rows in table

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

ToolJet - How To - Bulk update multiple rows in table

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

ToolJet - How To - Bulk update multiple rows in table

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