sqlUpdate

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

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

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.

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

Example 3: Group by store and sort by date to compute cumulative sales per store, but update only the records for stores whose monthly cumulative sales reach 50,000.

sales = table(
    `store101`store101`store101`store205`store205`store205 as storeId,
    2024.06.01 2024.06.02 2024.06.03 2024.06.01 2024.06.02 2024.06.03 as bizDate,
    18000.0 22000.0 15000.0 16000.0 12000.0 9000.0 as salesAmt,
    take(double(NULL), 6) as cumSalesAmt
)
​
sqlUpdate(
    table=sales,
    updates=<cumsum(salesAmt) as cumSalesAmt>,
    contextBy=<storeId>,
    csort=<bizDate>,
    having=<sum(salesAmt) >= 50000>
).eval()
​
sales

Output:

• having can only be used with contextBy.
• For store101, the group’s total sales amount is 55,000, which meets sum(salesAmt) ≥ 50,000, so cumSalesAmt is updated with the cumulative values sorted by bizDate.
• For store205, the group’s total sales amount is 37,000, which does not meet the threshold, so this group’s records are not updated, and cumSalesAmt remains NULL.