Exec, update, and delete statements¶

This page shows you how to use the qSQL exec, update, and delete statements, through examples on in-memory tables.

The three statements share their syntax with select: the select phrase, the where clause, and the by phrase work the same way, so this page covers only what's different. The examples use the same trade and master tables as How to select data.

Exec¶

exec works like select, but it returns a list, an atom, or a dictionary instead of a table. Use it when the next step needs plain q values, for example a vector to pass to a function. For the full syntax, see the exec reference.

Return a list or an atom¶

With one unnamed column, exec returns a list:

q)5#exec price from trade where sym=`AAPL
255.6 256.24 256.57 258.16 258.35

With an aggregation, it returns an atom:

q)exec size wavg price from trade where sym=`AAPL
270.8899

Naming even one result makes exec return a dictionary:

q)exec vwap:size wavg price from trade where sym=`AAPL
vwap| 270.8899

select returns the same values wrapped in a table, which you then need to index, for example with first select … or (select …)`price.

Use exec distinct to return the unique values as a list rather than as a one-column table:

q)exec distinct ex from trade where sym=`AAPL
`S`W`Q`Y`D`N`C`Z`M`I`X`J

Return a dictionary¶

With several columns, exec returns a dictionary from column names to values:

q)exec open:first price, close:last price from trade where sym=`AAPL
open | 255.6
close| 285.48

Unlike select, the columns don't need the same length, so you can mix atoms and lists:

q)exec avg price, distinct ex from trade where sym=`AAPL
price| 271.5339
ex   | `S`W`Q`Y`D`N`C`Z`M`I`X`J

With a by phrase, the keys of the dictionary are the groups:

q)exec last price by sym from trade where sym in `AAPL`MSFT
AAPL| 285.48
MSFT| 562.95

To build a dictionary from two columns, use ! in the select phrase, as in exec sym!description from master. Make sure the left column is unique if you intend to use the result as a lookup.

With no select phrase, exec returns the last row as a dictionary.

Update¶

update adds columns to a table or overwrites existing ones, and returns the whole table, not only the rows or columns you change. For the full syntax, see the update reference.

Add or overwrite a column¶

Name the column in the select phrase. A new name adds a column, and an existing name overwrites it. The expression can be anything you could write in a select, as described in Derive new columns:

q)3#update lot:?[size>80;`large;`small] from trade
sym  time                 price  size stop cond ex lot
--------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   0    B    L  small
PRU  0D09:30:00.153776659 103.02 20   0    B    J  small
TSLA 0D09:30:00.167902241 463.29 27   0    E    V  small

Overwriting a whole column can change its type. For example, update size:size%100 from trade turns size from long to float.

Update some rows¶

The where clause chooses which rows to change; see Filter rows. The other rows keep their values, and in a new column they get nulls:

q)select[3] time, size, lot from (update lot:`large from trade where size>80) where sym=`AAPL
time                 size lot
-------------------------------
0D09:30:22.699448654 44
0D09:32:29.061196651 90   large
0D09:33:26.493750743 37

Because the other rows keep their values, the new values must have the type of the existing column. Here size is a long column, so a float signals type:

q)update size:0.5 from trade where sym=`AAPL
'type

Update each group¶

With a by phrase, update calculates the expression for each group separately and writes the results back to the group's rows, in their original positions. An aggregation repeats its result on every row of the group:

q)select[4] sym, time, size, avgSize from update avgSize:avg size by sym from trade
sym  time                 size avgSize
---------------------------------------
CUZ  0D09:30:00.011627745 65   55.70492
PRU  0D09:30:00.153776659 20   54.06818
TSLA 0D09:30:00.167902241 27   54.47917
EMA  0D09:30:01.205070767 12   52.81132

The by phrase matters for expressions that refer to other rows, such as prev or sums. The rows of trade are in time order across all stocks, so without by, prev price is the price of the previous trade in any stock:

q)select[3] sym, time, price, change from (update change:price-prev price from trade) where sym=`AAPL
sym  time                 price  change
---------------------------------------
AAPL 0D09:30:22.699448654 255.6  252.69
AAPL 0D09:32:29.061196651 256.24 67.48
AAPL 0D09:33:26.493750743 256.57 180.02

With by sym, it's the price of the previous trade in the same stock:

q)select[3] sym, time, price, change from (update change:price-prev price by sym from trade) where sym=`AAPL
sym  time                 price  change
---------------------------------------
AAPL 0D09:30:22.699448654 255.6
AAPL 0D09:32:29.061196651 256.24 0.64
AAPL 0D09:33:26.493750743 256.57 0.33

Delete¶

delete removes rows or columns from a table. For the full syntax, see the delete reference.

Delete rows¶

Use a where clause to choose the rows to remove. The clause works as in Filter rows:

q)count trade
2619
q)count delete from trade where size<15
2455

With no where clause, delete removes every row. The result is an empty table that keeps the columns and their types, which is useful as a schema:

q)meta delete from trade
c    | t f a
-----| -----
sym  | s
time | n
price| f
size | j
stop | b
cond | c
ex   | s

Don't rely on column attributes being preserved by delete; inspect the result and reapply any required attributes.

Delete columns¶

List the columns to remove:

q)cols delete stop, cond from trade
`sym`time`price`size`ex

A delete statement removes either rows or columns, not both, so you can't combine a column list with a where clause:

q)delete stop from trade where sym=`AAPL
'nyi

delete doesn't take a by phrase.

delete also removes entries from a dictionary, and variables from a namespace, for example delete x from `.. See the delete reference.

Change a table in place¶

update and delete don't change the table you query. They return a modified copy, and the original stays as it was:

q)count delete from trade where size<15
2455
q)count trade
2619

To change the table itself, refer to it by name, as a symbol. The statement then modifies the table in place and returns its name:

q)update vwap:size wavg price by sym from `trade
`trade
q)3#select sym, price, size, vwap from trade
sym  price  size vwap
-------------------------
CUZ  29.06  65   29.94107
PRU  103.02 20   107.6741
TSLA 463.29 27   474.214
q)delete vwap from `trade
`trade

This works for global tables in memory. On a table mapped from disk, update and delete return a copy and leave the files unchanged, and on a partitioned table they signal par. To change data on disk, see Database maintenance. For how all four statements behave on disk, see Querying kdb+ data.

Next steps¶

How to select data: the select phrase, where clause, and by phrase that these statements share.
Functional qSQL: build exec, update, and delete statements whose columns are decided at runtime.