tableUpsert

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

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

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.

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.

• Parameters keyColNames and sortColumns are not supported for DFS tables in the PKEY database.

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

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

Set ignoreNull = true for a PKEY table. When an existing record is updated, null values in updateData do not overwrite the corresponding values in the target table.

if(existsCatalog(catalog="tableUpsertDemo")){
    dropCatalog(catalog="tableUpsertDemo")
}
createCatalog(catalog="tableUpsertDemo")
go
create database tableUpsertDemo.productInventory partitioned by VALUE(1..2), engine="PKEY"
go
create table tableUpsertDemo.productInventory.productState(
    area INT,
    id LONG,
    name STRING,
    price DOUBLE,
    qty INT
)
partitioned by area,
primaryKey=`area`id
go
append!(obj=tableUpsertDemo.productInventory.productState, newData=table(1 as area, 1l as id, "A" as name, 10.0 as price, 100 as qty))
updateData = table(1 as area, 1l as id, string(NULL) as name, 99.0 as price, int(NULL) as qty)
tableUpsert(obj=tableUpsertDemo.productInventory.productState, newData=updateData, ignoreNull=true)
result = select * from tableUpsertDemo.productInventory.productState
dropCatalog(catalog="tableUpsertDemo")
result