sqlUpdate
Syntax
sqlUpdate(table, updates, [from], [where], [contextBy])
Arguments
Each parameter refers to the corresponding part of the SQL update statement:
update
[table_name]
set col1=X1, [col2=X2,…]
[from table_joiner(table_names)]
[where condition(s)]
[context by col_name(s)]
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 is a metacode indicating the table join operation.
where is a metacode indicating the where condition.
contextBy is a metacode indicating the context by clause.
Details
Dynamically generate a metacode of the SQL update statement. To execute the generated metacode, please use function eval.
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 |
Example 2. Update a distributed 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;
250000