upsert!

upsert!(obj, newData, [ignoreNull=false], [keyColNames], [sortColumns])

obj is a keyed table, indexed table, or a DFS table.

newData is an in-memory table.

ignoreNull (optional) is a Boolean value. If set to true, for the NULL values in newData, the corresponding elements in obj are not updated. The default value is false.

keyColNames (optional) is a STRING scalar/vector. When obj is a DFS table, keyColNames and the partitioning columns are considered as the key columns.

sortColumns (optional) is a STRING scalar or vector. The updated partitions will be sorted on sortColumns (only within each partition, not across partitions).

Insert rows into a keyed table or indexed table if the values of the primary key do not already exist, or update them if they do.

upsert! a keyed table:

sym=`A`B`C
date=take(2021.01.06, 3)
x=1 2 3
y=5 6 7
t=keyedTable(`sym`date, sym, date, x, y)
t;

newData = table(`A`B`C`D as sym, take(2021.01.06, 4) as date, NULL NULL 300 400 as x, NULL 600 700 800 as y);
newData;

upsert!(t, newData, ignoreNull=true)
t;

The example above is for the case when ignoreNull = true. Compare it with the following example where ignoreNull = false (by default).

sym=`A`B`C
date=take(2021.01.06, 3)
x=1 2 3
y=5 6 7
t=keyedTable(`sym`date, sym, date, x, y)
upsert!(t, newData)
t;

upsert! a DFS table:

ID=0 1 2 2
x=0.1*0..3
t=table(ID, x)
db=database("dfs://rangedb128", VALUE,  0..10)
pt=db.createPartitionedTable(t, `pt, `ID)
pt.append!(t)
select * from pt;

t1=table(1 as ID, 111 as x)
upsert!(pt, t1, keyColNames=`ID)
select * from pt;

t1=table(2 as ID, 222 as x)
upsert!(pt, t1, keyColNames=`ID)
select * from pt;

Use upsert! to update a DFS table. If ignoreNull = true, the records in the target table corresponding to the NULL value of the new data will not be updated.

if(existsDatabase("dfs://valuedemo")) {
  dropDatabase("dfs://valuedemo")
}
db = database("dfs://valuedemo", VALUE, 1..10)
t = table(take(1..10, 100) as id, 1..100 as id2, 100..1 as value)
pt = db.createPartitionedTable(t, "pt", `id).append!(t)
t2 = table( 1 2 as id, 1 2 as id2, 1 NULL as value)
upsert!(pt, t2, true, "id2")

if(existsDatabase("dfs://upsert")) {
  dropDatabase("dfs://upsert")
}
sym=`A`B`C`A`D`B`A
date=take(2021.12.10,3) join take(2021.12.09, 3) join 2021.12.10
price=8.3 7.2 3.7 4.5 6.3 8.4 7.6
val=10 19 13 9 19 16 10
t=table(sym, date, price, val)
db=database("dfs://upsert", VALUE,  `A`B`C)
pt=db.createPartitionedTable(t, `pt, `sym)
pt.append!(t)
t1=table(`A`B`E as sym, take(2021.12.09, 3) as date, 11.1 10.5 6.9 as price, 12 9 11 as val)
upsert!(pt, t1, keyColNames=`sym, sortColumns=`date`val)
select * from pt