sqlUpdate
Syntax
sqlUpdate(table, updates, [from], [where], [contextBy], [csort], [ascSort],
[having])
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
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 |
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