# sqlUpdate {#sqlupdate}

## Syntax {#syntax}

`sqlUpdate(table, updates, [from], [where], [contextBy], [csort], [ascSort], [having])`

## Arguments {#arguments}

**table** can be an in-memory table or a distributed table.

**update** is a metacode or a tuple of metacode, indicating the updating operation.

**from** \(optional\) is a metacode indicating the table join operation.

**where** \(optional\) is a metacode indicating the where condition.

**contextBy** \(optional\) is a metacode indicating the context by clause.

**csort** \(optional\) is metacode or a tuple of metacode that specifies the column name\(s\) followed by `csort`. This parameter only works when *contextBy*is specified.

**ascSort**\(optional\) is a scalar or vector indicating whether each *csort*column is sorted in ascending or descending order. 1 \(default\) means ascending and 0 means descending.

**having**\(optional\) is metacode or a tuple of metacode that specifies the `having` condition\(s\). This parameter only works when *contextBy*is specified.

## Details {#details}

Dynamically generate a metacode of the SQL update statement. To execute the generated metacode, please use function [eval](../e/eval.md).

## Examples {#examples}

Example 1. Update the records in an in-memory table

```
t1=table(`A`A`B`B as symbol, 2021.04.15 2021.04.16 2021.04.15 2021.04.16 as date, 12 13 21 22 as price)
t2=table(`A`A`B`B as symbol, 2021.04.15 2021.04.16 2021.04.15 2021.04.16 as date, 10 20 30 40 as volume);

sqlUpdate(t1, <price*2 as updatedPrice>).eval()
t1;
```

|symbol|date|price|updatedPrice|
|------|----|-----|------------|
|A|2021.04.15|12|24|
|A|2021.04.16|13|26|
|B|2021.04.15|21|42|
|B|2021.04.16|22|44|

```
sqlUpdate(table=t1, updates=[<price*10 as updatedPrice>,<price*20 as updatedPrice2>]).eval()
t1;
```

|symbol|date|price|updatedPrice|updatedPrice2|
|------|----|-----|------------|-------------|
|A|2021.04.15|12|120|240|
|A|2021.04.16|13|130|260|
|B|2021.04.15|21|210|420|
|B|2021.04.16|22|220|440|

```
sqlUpdate(table=t2, updates=<cumsum(volume) as cumVolume>, contextby=<symbol>).eval()
t2;
```

|symbol|date|volume|cumVolume|
|------|----|------|---------|
|A|2021.04.15|10|10|
|A|2021.04.16|20|30|
|B|2021.04.15|30|30|
|B|2021.04.16|40|70|

```
sqlUpdate(table=t1, updates=<updatedPrice*volume as dollarVolume>, from=<lj(t1, t2, `symbol`date)>).eval()
t1;
```

|symbol|date|price|updatedPrice|dollarVolume|
|------|----|-----|------------|------------|
|A|2021.04.15|12|120|1200|
|A|2021.04.16|13|130|2600|
|B|2021.04.15|21|42|1260|
|B|2021.04.16|22|44|1760|

``` {#codeblock_q5v_bjy_2bc}
sqlUpdate(table=t2,updates=<cumsum(volume) as cumVolume>,contextBy=<symbol>,csort=<volume>,ascSort=0).eval()
t2;
```

|symbol|date|volume|cumVolume|
|------|----|------|---------|
|A|2021.04.15|10|30|
|A|2021.04.16|20|20|
|B|2021.04.15|30|70|
|B|2021.04.16|40|40|

Example 2. Update a DFS table

```
if(existsDatabase("dfs://db1")){
    dropDatabase("dfs://db1")
}
n=1000000
t=table(take(`A`B`C`D,n) as symbol, rand(10.0, n) as value)
db = database("dfs://db1", VALUE, `A`B`C`D)
Trades = db.createPartitionedTable(t, "Trades", "symbol")
Trades.append!(t)
x=exec sum(value) from Trades;

Trades=loadTable("dfs://db1", "Trades")
sqlUpdate(table=Trades, updates=<value+1 as value>, where=<symbol=`A>).eval()
y=exec sum(value) from Trades;

y-x;

// output: 250000
```

