# How to Update Data in a Retool Table

- Tool: Retool
- Difficulty: Beginner
- Time required: 20-25 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Enable inline table editing by toggling 'Editable' on individual columns in the Table Inspector. Changed cells accumulate in {{ table1.changesetArray }} as an array of modified rows. Add a Save Changes button that triggers a SQL UPDATE query using table1.changesetArray. For new rows, read {{ table1.newRows }} and run an INSERT query.

## Inline Editing and Bulk Updates in Retool Tables

Retool's Table component supports inline editing — users click a cell and type directly in the table without opening a separate form. Changes accumulate in the table's pending state and are not committed to the database until the user clicks a Save Changes button you configure.

This tutorial covers the complete editable table workflow: enabling editable columns, understanding the changesetArray structure, writing the SQL UPDATE query that processes bulk changes, handling new row insertion with newRows, and adding validation before saving.

The editable table pattern is ideal for spreadsheet-style data entry workflows where users need to edit many rows quickly. For complex edit scenarios with dependent fields or file uploads, a modal edit form (covered in the create-modal-dialogs tutorial) is more appropriate.

## Before you start

- A Retool app with a Table component displaying data from a SQL query
- A database resource with write permissions
- Basic familiarity with SQL UPDATE statements and JS Queries

## Step-by-step guide

### 1. Enable editable columns in the Table Inspector

Select the Table component. In the Inspector, go to the Columns section. For each column you want to make editable, find the column entry and toggle 'Editable' to ON. You can also configure the input type for editable columns: Text (default), Number, Select (dropdown with options), Date, Switch (boolean), or Custom. Set appropriate column types to get the right editor widget when users click the cell.

```
// Column configuration in Table Inspector:
// full_name column:
//   Editable: ON
//   Input type: Text
//   Placeholder: 'Enter full name'

// status column:
//   Editable: ON
//   Input type: Select
//   Options: {{ [{ label: 'Active', value: 'active' }, { label: 'Inactive', value: 'inactive' }] }}

// price column:
//   Editable: ON
//   Input type: Number
//   Min: 0

// id column:
//   Editable: OFF (always — never let users edit primary keys)
```

**Expected result:** Editable columns display an edit cursor when hovered. Clicking a cell opens an inline editor appropriate for the column type.

### 2. Understand the changesetArray structure

When users edit cells, the Table component accumulates changes in {{ table1.changesetArray }}. This is an array of objects, one per modified row. Each object contains the row's original data plus only the changed columns. The structure allows you to build a SQL UPDATE that processes only rows that were actually modified, not the entire table.

```
// table1.changesetArray structure:
// Each element represents one modified row
[
  {
    id: 42,                    // primary key (original)
    full_name: 'Jane Smith',   // updated value
    // other columns unchanged — only changed fields appear
  },
  {
    id: 17,
    status: 'inactive',        // updated value
    price: 299.99,             // also updated in this row
  }
]

// Access in expressions:
{{ table1.changesetArray }}
{{ table1.changesetArray.length }}
// Returns number of modified rows

// Has pending changes:
{{ table1.changesetArray.length > 0 }}

// Check if any row has a specific column changed:
{{ table1.changesetArray.some(r => 'price' in r) }}
```

**Expected result:** After editing cells, {{ table1.changesetArray.length }} returns the number of rows with pending changes.

### 3. Add a Save Changes button and configure it

Add a Button component above or below the Table with label 'Save Changes'. In the Inspector, set its Disabled property to {{ table1.changesetArray.length === 0 }} so it is greyed out when there are no pending changes. Set its Loading State property to {{ saveChanges.isFetching }} so a spinner shows during the save operation. Wire its Click event to trigger a JS Query named saveChanges.

```
// Save Changes button Inspector settings:
// Label: {{ `Save Changes (${table1.changesetArray.length})` }}
//        Shows count of pending changes
// Disabled: {{ table1.changesetArray.length === 0 }}
// Loading State: {{ saveChanges.isFetching }}

// Optional: Add a Discard Changes button
// Label: 'Discard'
// Disabled: {{ table1.changesetArray.length === 0 }}
// Click event: Control component → table1 → discardChanges
```

**Expected result:** Save Changes button is disabled when no edits are pending. Clicking it shows a loading state during the save.

### 4. Write the bulk UPDATE query using changesetArray

Create a Resource Query named updateRows (or configure inline in a JS Query). The UPDATE query needs to process each row in changesetArray. The most reliable approach for SQL databases is a series of individual UPDATE statements executed in sequence from a JS Query, or a single statement with CASE expressions. For PostgreSQL, use unnest() with a multi-column UPDATE. The JS Query approach with individual updates per row is the clearest and most database-agnostic.

```
// JS Query: saveChanges
// Trigger: Save Changes button click

const changes = table1.changesetArray;

if (changes.length === 0) return;

try {
  // Trigger the SQL query once per changed row
  // (Use Promise.all for parallel execution)
  await Promise.all(
    changes.map(row =>
      updateSingleRow.trigger({
        additionalScope: {
          id: row.id,
          fullName: row.full_name ?? null,
          email: row.email ?? null,
          status: row.status ?? null,
          price: row.price ?? null,
        }
      })
    )
  );

  utils.showNotification({
    title: 'Saved',
    description: `${changes.length} row${changes.length > 1 ? 's' : ''} updated.`,
    notificationType: 'success',
  });

  await getTableData.trigger(); // Refresh

} catch (err) {
  utils.showNotification({
    title: 'Save failed',
    description: err.message,
    notificationType: 'error',
  });
  throw err;
}
```

**Expected result:** Clicking Save Changes updates all modified rows in the database and refreshes the table.

### 5. Write the SQL UPDATE query for a single row

Create a SQL Resource Query named updateSingleRow that takes the row fields as parameters from additionalScope. Use COALESCE to only update columns that were actually changed — pass null for unchanged columns and the query uses the existing database value. This prevents overwriting columns that were not part of the edit.

```
-- SQL query: updateSingleRow
-- Mode: Manual (triggered from JS Query)
UPDATE customers
SET
  full_name = COALESCE({{ fullName }}, full_name),
  email = COALESCE({{ email }}, email),
  status = COALESCE({{ status }}, status),
  price = COALESCE({{ price }}, price),
  updated_at = NOW()
WHERE id = {{ id }}
RETURNING id, full_name, email, status, price, updated_at;

-- COALESCE: if the parameter is null, keep the existing column value
-- This means passing null for 'fullName' leaves full_name unchanged
```

**Expected result:** Each changed row is updated in the database. RETURNING shows the post-update values including updated_at.

### 6. Handle new row insertion with table1.newRows

The Table component also supports adding new rows inline. Enable 'Allow adding rows' in the Table Inspector. When users click '+ Add row', a blank row appears. New rows are accessible via {{ table1.newRows }}. Write a JS Query that reads table1.newRows and runs an INSERT for each new row, similar to the UPDATE pattern.

```
// JS Query: saveNewRows
// Trigger: separate 'Save New Rows' button or combine with saveChanges

const newRows = table1.newRows;

if (newRows.length === 0) return;

try {
  await Promise.all(
    newRows.map(row =>
      insertSingleRow.trigger({
        additionalScope: {
          fullName: row.full_name,
          email: row.email,
          status: row.status || 'active',
          price: row.price,
        }
      })
    )
  );

  utils.showNotification({
    title: 'Rows inserted',
    description: `${newRows.length} new row${newRows.length > 1 ? 's' : ''} added.`,
    notificationType: 'success',
  });

  await getTableData.trigger();

} catch (err) {
  utils.showNotification({
    title: 'Insert failed',
    description: err.message,
    notificationType: 'error',
  });
  throw err;
}
```

**Expected result:** New rows added via the inline '+ Add row' feature are inserted into the database. {{ table1.newRows.length }} shows pending new rows.

### 7. Add row-level validation before saving

Before saving, validate that required columns in changesetArray have non-empty values. Add validation logic to the saveChanges JS Query that checks each changed row for required fields. Display field-specific error notifications so users know exactly which rows need attention.

```
// JS Query: saveChanges (with validation)
const changes = table1.changesetArray;

if (changes.length === 0) return;

// Validate required fields in changed rows
const invalidRows = changes.filter(row => {
  // Check if any of the modified fields have invalid values
  if ('full_name' in row && !row.full_name?.trim()) return true;
  if ('email' in row && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(row.email || '')) return true;
  if ('price' in row && (row.price < 0 || row.price === null)) return true;
  return false;
});

if (invalidRows.length > 0) {
  utils.showNotification({
    title: 'Validation failed',
    description: `${invalidRows.length} row${invalidRows.length > 1 ? 's have' : ' has'} invalid values. Please fix highlighted cells.`,
    notificationType: 'error',
  });
  return;
}

// Proceed with save...
```

**Expected result:** Attempting to save rows with empty required fields shows a validation error and does not proceed to the UPDATE query.

## Complete code example

File: `JS Query: saveChanges (complete)`

```javascript
// saveChanges — validates, bulk-updates changesetArray, and handles new rows
// Trigger: Save Changes button click
// Requires: updateSingleRow (SQL), insertSingleRow (SQL), getTableData (SQL)

const changes = table1.changesetArray;
const newRows = table1.newRows || [];
const totalOperations = changes.length + newRows.length;

if (totalOperations === 0) {
  utils.showNotification({
    title: 'No changes',
    description: 'Make edits to rows before saving.',
    notificationType: 'info',
  });
  return;
}

// Validate changed rows
const invalidRows = changes.filter(row => {
  if ('full_name' in row && !row.full_name?.trim()) return true;
  if ('email' in row && row.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(row.email)) return true;
  return false;
});

// Validate new rows
const invalidNewRows = newRows.filter(row => {
  return !row.full_name?.trim() || !row.email?.trim();
});

if (invalidRows.length > 0 || invalidNewRows.length > 0) {
  utils.showNotification({
    title: 'Validation failed',
    description: `${invalidRows.length + invalidNewRows.length} row(s) have missing required fields.`,
    notificationType: 'error',
  });
  return;
}

try {
  // Run updates and inserts in parallel
  await Promise.all([
    ...changes.map(row =>
      updateSingleRow.trigger({
        additionalScope: {
          id: row.id,
          fullName: row.full_name ?? null,
          email: row.email ?? null,
          status: row.status ?? null,
        }
      })
    ),
    ...newRows.map(row =>
      insertSingleRow.trigger({
        additionalScope: {
          fullName: row.full_name,
          email: row.email,
          status: row.status || 'active',
        }
      })
    ),
  ]);

  utils.showNotification({
    title: 'Saved successfully',
    description: `${changes.length} updated, ${newRows.length} inserted.`,
    notificationType: 'success',
  });

  await getTableData.trigger();

} catch (err) {
  utils.showNotification({
    title: 'Save failed',
    description: err.message,
    notificationType: 'error',
  });
  throw err;
}
```

## Common mistakes

- **Running the UPDATE for every row in the table instead of only rows in changesetArray — updates rows that were not changed and causes unnecessary database writes** — undefined Fix: Iterate only over table1.changesetArray, not table1.data. changesetArray contains only the rows that the user actually modified.
- **Not refreshing the table query after saving — the table shows the user's typed values instead of the canonical database values, including any server-side modifications** — undefined Fix: After a successful save, always call await getTableData.trigger() to reload the data from the database into the table.
- **Expecting changesetArray to include all column values for a modified row — it only includes the primary key and the columns that were actually changed** — undefined Fix: When building the UPDATE SQL, use COALESCE({{ changedColumnValue }}, existing_column) to default to the existing database value for columns not present in the changeset.
- **Trying to use a transformer to process changesetArray and trigger the update — transformers are read-only and cannot trigger queries** — undefined Fix: All mutation logic must be in a JS Query. Transformers can only compute derived values from existing state, not trigger side effects.

## Best practices

- Never make primary key (id) columns editable — this prevents accidental data corruption from users editing record identifiers
- Show the pending change count in the Save button label: {{ 'Save Changes (' + table1.changesetArray.length + ')' }}
- Add a Discard Changes button that calls the Table component's discardChanges action to let users back out of unwanted edits
- Use COALESCE in the UPDATE SQL to only overwrite columns that were actually changed — passing null preserves the existing database value
- Run multiple row updates in parallel with Promise.all() for better performance on bulk saves
- Validate changesetArray rows before submitting — check required fields and data types to prevent database constraint errors
- Always refresh the data query after saving to display the canonical database values (including server-side defaults like updated_at)

## Frequently asked questions

### Does table1.changesetArray include the original values or just the new values for changed cells?

changesetArray includes the row's primary key and only the columns that changed with their new values. It does not include unchanged columns or the original pre-edit values. If you need to compare old vs new values for auditing, you would need to store the original data separately in a Temporary State before users start editing.

### Can I automatically save changes when a user finishes editing a cell, without a Save button?

Yes — add a Row change event handler to the Table component (Inspector → Interaction → Row change). Set it to trigger the updateSingleRow query directly with the changed row's data. This creates an auto-save-on-edit UX but removes the ability for users to discard changes. Use this pattern for high-trust internal tools where immediate persistence is desired.

### How do I highlight rows that have unsaved changes in the table?

In the Table Inspector → Columns, add a calculated column or use Row Color. Set the Row Color expression to check if the current row's ID appears in table1.changesetArray: {{ table1.changesetArray.some(r => r.id === currentRow.id) ? '#fffbeb' : 'transparent' }}. This highlights edited rows in yellow until they are saved.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-update-data-in-a-retool-table
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-update-data-in-a-retool-table