Update Query

The update function modifies columns in a Table using a Dictionary syntax similar to the Select Query.

(set employees (table [name dept salary] 
  (list 
    (list "Alice" "Bob" "Charlie") 
    ['IT 'HR 'IT] 
    [75000 65000 85000])))

(set employees (update {
  salary: (* salary 1.1)
  from: employees
  where: (> salary 70000)}))

(select {name: name salary: salary from: employees})
┌─────────┬────────┐
│ name    │ salary │
├─────────┼────────┤
│ Alice   │ 82500  │
│ Bob     │ 65000  │
│ Charlie │ 93500  │
└─────────┴────────┘

All update parameters are provided as a single Dictionary. The from clause is required.

Column Name Conflicts

If a column name matches a built-in function or environment variable, use (at table 'column) to access it:

(update {
  name: (at employees 'name)
  from: employees})

Filtering with where

Use the where clause to update only rows that match a condition. The where clause accepts any boolean expression that evaluates to a Vector of Booleans or a single boolean value.

(set employees (update {
  salary: (* salary 1.1)
  from: employees
  where: (> salary 55000)}))

You can use complex conditions with logical operators:

(set employees (update {
  salary: (+ salary 5000)
  from: employees
  where: (and (= dept 'IT) (> salary 70000))}))

Grouping with by

Group rows using the by keyword to apply updates per group. The by clause accepts a Symbol or a Dictionary of symbols.

Single Column Grouping

(set employees (update {
  salary: (+ salary 1000)
  from: employees
  by: dept
  where: (> salary 55000)}))

Multiple Column Grouping

Group by multiple columns using a Dictionary:

(set trades (update {
  price: (* price 1.05)
  from: trades
  by: {dept: dept region: region}}))

In-Place Update

To modify the table in place without reassigning, pass the table name as a quoted Symbol in the from clause:

(update {
  salary: (* salary 1.1)
  from: 'employees
  where: (> salary 55000)})

Important: Persisting Changes

By default, update returns a new table and does not modify the original. To persist changes, you have two options:

1. Reassign the result:

   (set employees (update {... from: employees}))
   

2. Use in-place update with quoted symbol:

   (update {... from: 'employees})  ;; Modifies table directly