# 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`](select.md): 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](select.md#example-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](../../ref/exec.md).

### Return a list or an atom

With one unnamed column, `exec` returns a list:
```q
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
q)exec size wavg price from trade where sym=`AAPL
270.8899
```

Naming even one result makes `exec` return a dictionary:
```q
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
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
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
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
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 [`!`](../../ref/dict.md) 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](../../ref/update.md).

### 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](select.md#derive-new-columns):
```q
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](select.md#filter-rows). The other rows keep their values, and in a new column they get nulls:
```q
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
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
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`](../../ref/next.md#prev) or [`sums`](../../ref/sum.md#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
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
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](../../ref/delete.md).

### Delete rows

Use a `where` clause to choose the rows to remove. The clause works as in [Filter rows](select.md#filter-rows):
```q
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
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

To select all the columns of a table except a few, delete the ones you don't want. This is shorter and less error-prone than listing every column you want in a `select`, especially when the table has many columns. List the columns to remove:
```q
q)cols delete stop, cond from trade
`sym`time`price`size`ex
```

`delete` doesn't modify `trade` in place. It returns a copy without those columns. It also doesn't work on historical databases, where the table is stored on disk. Use `select` with an explicit column list there.

A `delete` statement removes either rows or columns, not both, so you can't combine a column list with a `where` clause:
```q
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](../../ref/delete.md#namespace-objects).

## 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
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
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](../interact_with_databases/maintenance.md). For how all four statements behave on disk, see [Querying kdb+ data](overview.md#qsql).

## Next steps

- [How to select data](select.md): the select phrase, `where` clause, and `by` phrase that these statements share.
- [Functional qSQL](../../ref/funsql.md): build `exec`, `update`, and `delete` statements whose columns are decided at runtime.
