Skip to content

upsert

Add new records to a table

Syntax: x upsert y, upsert[x;y]

Where

  • x is a table, or the name of a table as a symbol atom, or the name of a splayed table as a directory handle
  • y is one or more records with types matching x, or a table with conforming columns

If x is the name of a table, it is updated in place. Otherwise the updated table is returned.

If x is the name of a table as a symbol atom (or the name of a splayed table as a directory handle) that does not exits in the file system, it is written.

Simple table

q)t:([] name:`tom`dick`harry; age:28 29 30)

q)t upsert (`dick;49)                           / single record
name  age
---------
tom   28
dick  29
harry 30
dick  49

q)t upsert ((`dick;49);(`jane;23))              / two records
name  age
---------
tom   28
dick  29
harry 30
dick  49
jane  23

q)`t upsert ([] name:`dick`jane; age:49 23)     / table
`t
q)t
name  age
---------
tom   28
dick  29
harry 30
dick  49
jane  23

Keyed table

If the table is keyed, any new records that match on key are updated. Otherwise, new records are inserted.

q)show a:([s:`q`w`e]r:1 2 3;u:5 6 7)
s| r u
-| ---
q| 1 5
w| 2 6
e| 3 7

q)a upsert ([s:`e`r`q]r:30 4 10;u:70 8 50)    / update `q and `e, insert new `r
s| r  u                                       / returning new table
-| -----
q| 10 50
w| 2  6
e| 30 70
r| 4  8

q)`a upsert ([s:`e`r`q]r:30 4 10;u:70 8 50)   / same but updating table in place
`a

Serialized table

q)`:data/tser set ([] c1:`a`b; c2:1.1 2.2)
`:data/tser
q)`:data/tser upsert (`c; 3.3)
`:data/tser

q)get `:data/tser
c1 c2
------
a  1.1
b  2.2
c  3.3

Upserting to a serialized table reads the table into memory, updates it, and writes it back to file.

Splayed table

q)`:data/tsplay/ set ([] c1:`sym?`a`b; c2:1.1 2.2)
`:data/tsplay/
q)`:data/tsplay upsert (`sym?`c; 3.3)
`:data/tsplay
q)select from `:data/tsplay
c1 c2
------
a  1.1
b  2.2
c  3.3

Upserting to a splayed table appends new values to the column files.

Upserting to a serialized or splayed table removes any attributes set.


Cond is not supported inside q-SQL expressions

Enclose in a lambda or use Vector Conditional instead.

insert, Join
Joins, q-SQL
Q for Mortals §9.2 Upsert