SQL
`ON CONFLICT` Clause
When the `ON CONFLICT ... DO UPDATE` clause is used and a conflict occurs, DuckDB internally assigns `NULL` values to the row's columns that are unaffected by the conflict, then re-assigns their values. If the affected columns use a `NOT NULL` constraint, this will trigger a `NOT NULL constraint failed` error. For example:  
```sql
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val1 DOUBLE, val2 DOUBLE NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, val1 DOUBLE);
INSERT INTO t1
VALUES (1, 2, 3);
INSERT INTO t2
VALUES (1, 5);

INSERT INTO t1 BY NAME (SELECT id, val1 FROM t2)
ON CONFLICT DO UPDATE
SET val1 = EXCLUDED.val1;
```  
This fails with the following error:  
```console
Constraint Error: NOT NULL constraint failed: t1.val2
```  
#### Defining a Conflict Target {#docs:sql:statements:insert::defining-a-conflict-target}  
A conflict target may be provided as `ON CONFLICT (conflict_target)`. This is a group of columns that an index or uniqueness/key constraint is defined on. If the conflict target is omitted, or `PRIMARY KEY` constraint(s) on the table are targeted.  
Specifying a conflict target is optional unless using a [`DO UPDATE`](#::do-update-clause-upsert) and there are multiple unique/primary key constraints on the table.  
```sql
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
VALUES (1, 20, 300);
SELECT * FROM tbl;
```  
| i | j  |  k  |
|--:|---:|----:|
| 1 | 20 | 300 |  
```sql
INSERT INTO tbl
VALUES (1, 40, 700)
ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;
```  
| i | j  |  k   |
|--:|---:|-----:|
| 1 | 20 | 1400 |  
```sql
INSERT INTO tbl
VALUES (1, 20, 900)
ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;
```  
| i | j  |  k   |
|--:|---:|-----:|
| 1 | 20 | 4500 |  
When a conflict target is provided, you can further filter this with a `WHERE` clause, that should be met by all conflicts.  
```sql
INSERT INTO tbl
VALUES (1, 40, 700)
ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;
```  
#### Multiple Tuples Conflicting on the Same Key {#docs:sql:statements:insert::multiple-tuples-conflicting-on-the-same-key}  
Currently, DuckDB’s `ON CONFLICT DO UPDATE` feature is limited to enforce constraints between committed and newly inserted (transaction-local) data.
In other words, having multiple tuples conflicting on the same key is not supported.
If the newly inserted data has duplicate rows, an error message will be thrown, or unexpected behavior can occur.
This also includes conflicts **only** within the newly inserted data.  
```sql
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl
VALUES (1, 84), (1, 168)
ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;
```  
This returns the following message.  
```console
Error: Invalid Input Error: ON CONFLICT DO UPDATE can not update the same row twice in the same command.
Ensure that no rows proposed for insertion within the same command have duplicate constrained values
```  
To work around this, enforce uniqueness using [`DISTINCT ON`](#docs:sql:query_syntax:select::distinct-on-clause). For example:  
```sql
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl
VALUES (1, 42);
INSERT INTO tbl
SELECT DISTINCT ON(i) i, j FROM VALUES (1, 84), (1, 168) AS t (i, j)
ON CONFLICT DO UPDATE SET j = j + EXCLUDED.j;
SELECT * FROM tbl;
```  
| i |  j  |
|--:|----:|
| 1 | 126 |