tableUpsert

Syntax

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

Arguments

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.

Details

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.

Note:

  • When using this function, please make sure the corresponding columns in table newData and obj are arranged in the same order, or the system may generate the wrong result or throw an error.

  • If obj is a DFS table with duplicated "keys" (as specified by keyColNames), tableUpsert on rows with duplicated keys only updates the first row.

  • The behavior of this function is controlled by the enableNullSafeJoin configuration:

    • When enableNullSafeJoin=true, joining on null values is allowed, and tableUpsert may update records that contain nulls.

    • When enableNullSafeJoin=false, joining on null values is not allowed, and tableUpsert will not update records with nulls.

Examples

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