sqlUpdate

sqlUpdate(table, updates, [from], [where], [contextBy])

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)] 
    [csort col_name(s) ASC|DESC]
    [having condition(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 (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.

Dynamically generate a metacode of the SQL update statement. To execute the generated metacode, please use function eval.

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;

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

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

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

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

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