update

Add or amend rows or columns of a table or entries in a dictionary

update is a qSQL query template and varies from regular q syntax.

For the Update operator !, see Functional SQL

Since 4.1t 2021.06.04 updates from splayed table and path@tablename now leverage peach to load columns (when running with secondary threads).

q)update x:0 from get`:mysplay

Syntax

update p_s [by p_b] from t_exp [where p_w]

qSQL query templates

From phrase

update will not modify a splayed table on disk.

Select phrase

Names in the Select phrase refer to new or modified columns in the table expression.

q)t:([] name:`tom`dick`harry; age:28 29 35)
q)update eye:`blue`brown`green from t
name  age eye
---------------
tom   28  blue
dick  29  brown
harry 35  green

Where phrase

The Where phrase restricts the scope of updates.

q)t:([] name:`tom`dick`harry; hair:`fair`dark`fair; eye:`green`brown`gray)
q)t
name  hair eye
----------------
tom   fair green
dick  dark brown
harry fair gray

q)update eye:`blue from t where hair=`fair
name  hair eye
----------------
tom   fair blue
dick  dark brown
harry fair blue

New values must have the type of the column being amended.

If the query adds a new column it will have values only as determined by the Where phrase. At other positions, it will have nulls of the column’s type.

By phrase

The By phrase applies the update along groups. This is most useful with aggregate and uniform functions.

With an aggregate function, the entire group gets the value of the aggregation on the group.

q)update avg weight by city from p
p | name  color weight city
--| -------------------------
p1| nut   red   15     london
p2| bolt  green 14.5   paris
p3| screw blue  17     rome
p4| screw red   15     london
p5| cam   blue  14.5   paris
p6| cog   red   15     london

A uniform function is applied along the group in place. This can be used, for example, to compute cumulative volume of orders.

q)update cumqty:sums qty by s from sp
s p  qty cumqty
---------------
0 p1 300 300
0 p2 200 500
0 p3 400 900
0 p4 200 1100
3 p5 100 100
0 p6 100 1200
1 p1 300 300
1 p2 400 700
2 p2 200 200
3 p2 200 300
3 p4 300 600
0 p5 400 1600

Since 4.1 2024.04.29 throws type error if dictionary update contains by clause (previously ignored).

Cond

Cond is not supported inside query templates: see qSQL.

---

delete, exec, select
qSQL, Functional SQL
Q for Mortals §9.5 The update template