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)]

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.

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;

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;

// output
250000