tableUpsert

tableUpsert(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 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).

Note:

• sortColumns is supported in upsert! only with the OLAP engine.

• To specify sortColumns, obj must be a DFS table.

• When obj is an empty table, setting sortColumns has no effect. That is, the system will not sort the inserted data.

Insert rows into a table if the values with the key do not already exist, or update them if they do.

Return value: A LONG pair. The first element represents the number of inserted records, and the second represents the number of updated records.

The usage of tableUpsert and upsert! is consistent, with the difference being that tableUpsert returns the number of involved records, while upsert! performs an in-place modification, which is often used to chain operations.

Use tableUpsert on 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)
newData = table(`A`B`C`D as sym1, take(2021.01.06, 4) as date1, NULL NULL 300 400 as x1, NULL 600 700 800 as y1);
tableUpsert(t, newData, ignoreNull=true)
// output
1:2

Used tableUpsert on an indexed table:

sym=`A`B`C
date=take(2021.01.06, 3)
x=1 2 3
y=5 6 7
t=indexedTable(`sym`date, sym, date, x, y)
newData = table(`A`B`C`D as sym1, take(2021.01.06, 4) as date1, NULL NULL 300 400 as x1, NULL 600 700 800 as y1);
tableUpsert(t, newData, ignoreNull=true)
// output
1:2

Use tableUpsert on 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)
t1=table(1 as ID, 111 as x)
tableUpsert(pt, t1, keyColNames=`ID)
// output
0:1