# upsert! {#upsert}

## Syntax {#syntax}

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

## Arguments {#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 {#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.

-   The behavior of this function is controlled by the *enableNullSafeJoin* configuration:
    -   When *enableNullSafeJoin*=true, joining on null values is allowed, and `upsert!` may update records that contain nulls.

    -   When enableNullSafeJoin=false, joining on null values is not allowed, and `upsert!` will not update records with nulls.


## Examples {#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|

