Upsert Query¶
The upsert function updates existing rows or inserts new ones in a Table based on key column(s). If a row with the same key exists, it is updated. Otherwise, a new row is inserted.
(set employees (table [id name age] (list [1 2] ['Alice 'Bob] [25 30])))
;; Upsert: id=2 exists, so it's updated; id=3 doesn't exist, so it's inserted
(set employees (upsert employees 1 (list [2 3] ['Bob-updated 'Charlie] [30 35])))
(select {id: id name: name age: age from: employees})
┌────┬─────────────┬─────┐
│ id │ name │ age │
├────┼─────────────┼─────┤
│ 1 │ Alice │ 25 │
│ 2 │ Bob-updated │ 30 │
│ 3 │ Charlie │ 35 │
├────┴─────────────┴─────┤
│ 3 rows (3 shown) │
└────────────────────────┘
The upsert function takes three arguments: the table to upsert into, the number of key columns (starting from the first column), the data to upsert
The data can be provided as a List, Dictionary, or another Table.
Understanding Key Columns¶
The first N columns (where N = number of key columns) form the key used to identify rows. The key determines whether a row should be updated or inserted
(set employees (table [id name age] (list [1 2] ['Alice 'Bob] [25 30])))
(set employees (upsert employees 1 (list [2 3] ['Bob-updated 'Charlie] [30 35])))
In this example, id is the key column (key_count = 1). The row with id=2 is updated, and a new row with id=3 is inserted.
Upserting a Single Row¶
Upserting Multiple Rows¶
Upsert multiple rows using a list of Vectors:
Upserting with Dictionary¶
Upsert rows using a Dictionary for clearer column mapping:
Upserting from Another Table¶
Upsert rows from another Table:
(set new_data (table [id name age] (list [8 9] ['Henry 'Ivy] [45 38])))
(set employees (upsert employees 1 new_data))
In-Place Upsertion¶
To modify the table in place without reassigning, pass the table name as a quoted Symbol:
Important: Persisting Changes
By default, upsert returns a new table and does not modify the original. To persist changes, you have two options:
-
Reassign the result:
-
Use in-place upsertion with quoted symbol: