Bulk Update Multiple Rows in Table - Tooljet

For the purpose of this guide, it's presumed that you've already established a successful connection to your data source. We'll use PostgreSQL for this example, but you can adjust the queries based on the SQL database that you are using.

1. Create a Query to Get the Data

Create a PostgreSQL query in SQL mode, rename it to users and enter the below code.

sql

SELECT * FROM <table name> // *replace <table name> with your table name*

Enable the Run the query on application load? option to execute the query automatically when the application starts.
Click on the Run button to fetch the data from the database.

2. Display the Data on the Table

Drag and drop a Table component onto the canvas from the components library on the right.
Click on the Table component to open its properties on the right sidebar.
To populate the Table with the data returned by the query, add the below code under the Data field of the Table:

{{queries.users.data}}

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.

4. Enable Multiple Row Selection

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

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. The query will be named as runjs1 by default.

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

Here the unique identifier is id and Table component's name is table1. You can update the unique identifier if you are using a different column as a unique identifier. You can also update the Table name if you have renamed it, the default name is table1.

6. Create an Update Query

Create a PostgreSQL query in SQL mode and rename it to update:

sql

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

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

7. Adding Event Handlers to Execute Queries in Sequence

Edit the Table component and add an 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.
Optionally, add loading state to the Table by clicking on fx next to the Loading state property.
Use the below code to show the loading state whenever a query is getting executed.

{{queries.users.isLoading || queries.update.isLoading}}

Now, go to the runjs1 query and add an event to run the update query for Query Success event. This will run the update query after the runjs1 query is successfully executed.

The data needs to reload once the update query runs since we want the Table component to be populated with the updated data.

Add a new event handler in the update query.
Select Query Success as the Event and Run Query as the Action.
Select users as Query.

This will refresh the table whenever the update query will be run.