How to Update Data in a Retool Table

1// Column configuration in Table Inspector:
2// full_name column:
3//   Editable: ON
4//   Input type: Text
5//   Placeholder: 'Enter full name'
6
7// status column:
8//   Editable: ON
9//   Input type: Select
10//   Options: {{ [{ label: 'Active', value: 'active' }, { label: 'Inactive', value: 'inactive' }] }}
11
12// price column:
13//   Editable: ON
14//   Input type: Number
15//   Min: 0
16
17// id column:
18//   Editable: OFF (always — never let users edit primary keys)

1// table1.changesetArray structure:
2// Each element represents one modified row
3[
4  {
5    id: 42,                    // primary key (original)
6    full_name: 'Jane Smith',   // updated value
7    // other columns unchanged — only changed fields appear
8  },
9  {
10    id: 17,
11    status: 'inactive',        // updated value
12    price: 299.99,             // also updated in this row
13  }
14]
15
16// Access in expressions:
17{{ table1.changesetArray }}
18{{ table1.changesetArray.length }}
19// Returns number of modified rows
20
21// Has pending changes:
22{{ table1.changesetArray.length > 0 }}
23
24// Check if any row has a specific column changed:
25{{ table1.changesetArray.some(r => 'price' in r) }}

1// Save Changes button Inspector settings:
2// Label: {{ `Save Changes (${table1.changesetArray.length})` }}
3//        Shows count of pending changes
4// Disabled: {{ table1.changesetArray.length === 0 }}
5// Loading State: {{ saveChanges.isFetching }}
6
7// Optional: Add a Discard Changes button
8// Label: 'Discard'
9// Disabled: {{ table1.changesetArray.length === 0 }}
10// Click event: Control component → table1 → discardChanges

1// JS Query: saveChanges
2// Trigger: Save Changes button click
3
4const changes = table1.changesetArray;
5
6if (changes.length === 0) return;
7
8try {
9  // Trigger the SQL query once per changed row
10  // (Use Promise.all for parallel execution)
11  await Promise.all(
12    changes.map(row =>
13      updateSingleRow.trigger({
14        additionalScope: {
15          id: row.id,
16          fullName: row.full_name ?? null,
17          email: row.email ?? null,
18          status: row.status ?? null,
19          price: row.price ?? null,
20        }
21      })
22    )
23  );
24
25  utils.showNotification({
26    title: 'Saved',
27    description: `${changes.length} row${changes.length > 1 ? 's' : ''} updated.`,
28    notificationType: 'success',
29  });
30
31  await getTableData.trigger(); // Refresh
32
33} catch (err) {
34  utils.showNotification({
35    title: 'Save failed',
36    description: err.message,
37    notificationType: 'error',
38  });
39  throw err;
40}

1-- SQL query: updateSingleRow
2-- Mode: Manual (triggered from JS Query)
3UPDATE customers
4SET
5  full_name = COALESCE({{ fullName }}, full_name),
6  email = COALESCE({{ email }}, email),
7  status = COALESCE({{ status }}, status),
8  price = COALESCE({{ price }}, price),
9  updated_at = NOW()
10WHERE id = {{ id }}
11RETURNING id, full_name, email, status, price, updated_at;
12
13-- COALESCE: if the parameter is null, keep the existing column value
14-- This means passing null for 'fullName' leaves full_name unchanged

1// JS Query: saveNewRows
2// Trigger: separate 'Save New Rows' button or combine with saveChanges
3
4const newRows = table1.newRows;
5
6if (newRows.length === 0) return;
7
8try {
9  await Promise.all(
10    newRows.map(row =>
11      insertSingleRow.trigger({
12        additionalScope: {
13          fullName: row.full_name,
14          email: row.email,
15          status: row.status || 'active',
16          price: row.price,
17        }
18      })
19    )
20  );
21
22  utils.showNotification({
23    title: 'Rows inserted',
24    description: `${newRows.length} new row${newRows.length > 1 ? 's' : ''} added.`,
25    notificationType: 'success',
26  });
27
28  await getTableData.trigger();
29
30} catch (err) {
31  utils.showNotification({
32    title: 'Insert failed',
33    description: err.message,
34    notificationType: 'error',
35  });
36  throw err;
37}

1// JS Query: saveChanges (with validation)
2const changes = table1.changesetArray;
3
4if (changes.length === 0) return;
5
6// Validate required fields in changed rows
7const invalidRows = changes.filter(row => {
8  // Check if any of the modified fields have invalid values
9  if ('full_name' in row && !row.full_name?.trim()) return true;
10  if ('email' in row && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(row.email || '')) return true;
11  if ('price' in row && (row.price < 0 || row.price === null)) return true;
12  return false;
13});
14
15if (invalidRows.length > 0) {
16  utils.showNotification({
17    title: 'Validation failed',
18    description: `${invalidRows.length} row${invalidRows.length > 1 ? 's have' : ' has'} invalid values. Please fix highlighted cells.`,
19    notificationType: 'error',
20  });
21  return;
22}
23
24// Proceed with save...