1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
# name: test/sql/upsert/upsert_coverage.test
# group: [upsert]
statement ok
PRAGMA enable_verification;
statement ok
CREATE TABLE tbl (a INT, b INT UNIQUE, c INT UNIQUE, d INT UNIQUE);
statement ok
INSERT INTO tbl (b, c, d) VALUES (1, 2, 3), (2, 3, 1), (3, 1, 2)
# Create conflicts in three existing tuples with one INSERT tuple.
# Each conflict has a different row ID.
# We never have to deal with one-to-many conflicts in anything other than
# a DO NOTHING action. That is because we need a conflict target
# to use any sort of WHERE or SET expression that references the existing table.
statement ok
INSERT INTO tbl(b, c, d) VALUES (3, 3, 3) ON CONFLICT DO NOTHING;
query III
SELECT b, c, d FROM tbl ORDER BY ALL;
----
1 2 3
2 3 1
3 1 2
# We fail without a conflict target.
statement error
INSERT INTO tbl(b, c, d) VALUES (3, 3, 3)
ON CONFLICT DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c, d = EXCLUDED.d;
----
<REGEX>:Binder Error.*Conflict target has to be provided for a DO UPDATE operation when the table has multiple UNIQUE/PRIMARY KEY constraints.*
|
