upsert!

Syntax

upsert!(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 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).

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 ignoreNull, keyColNames, sortColumns are not supported for DFS tables in the PKEY database.

Details

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.

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), upsert! on rows with duplicated keys only updates the first row.

Examples

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;
sym date x y
A 2021.01.06 1 5
B 2021.01.06 2 6
C 2021.01.06 3 7
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;
sym date x y
A 2021.01.06
B 2021.01.06 600
C 2021.01.06 300 700
D 2021.01.06 400 800
upsert!(t, newData, ignoreNull=true)
t;
sym date x y
A 2021.01.06 1 5
B 2021.01.06 2 600
C 2021.01.06 300 700
D 2021.01.06 400 800

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;
sym date x y
A 2021.01.06
B 2021.01.06 600
C 2021.01.06 300 700
D 2021.01.06 400 800

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;
ID x
0 0
1 0.1
2 0.2
2 0.3
t1=table(1 as ID, 111 as x)
upsert!(pt, t1, keyColNames=`ID)
select * from pt;
ID x
0 0
1 111
2 0.2
2 0.3
t1=table(2 as ID, 222 as x)
upsert!(pt, t1, keyColNames=`ID)
select * from pt;
ID x
0 0
1 111
2 222
2 0.3

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
sym date price val
A 2021.12.09 4.5 9
A 2021.12.09 11.1 12
A 2021.12.10 7.6 10
B 2021.12.09 10.5 9
B 2021.12.09 8.4 16
C 2021.12.10 3.7 13
D 2021.12.09 6.3 19
E 2021.12.09 6.9 11