Adding on to the other comments, I think the reason you can't do this is that each constraint could be violated by different rows.
So with... create table foo (a int unique, b int unique); insert into foo values (1, 3), (2, 2), (3, 1); ...then when trying... insert into foo values (1, 2) on conflict (a) or on conflict (b) do update ...; ...there are conflicts on both a and b, and each is from a different row. Do we update the (1, 3) row because of the (a) conflict, or update the (2, 2) row because of the (b) conflict? Both? None? The first based on the order they're written in the statement? So I think that ambiguity is why you get to pick one and only one constraint for an upsert. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz Sent: Wednesday, March 27, 2019 10:41 AM To: SQLite mailing list Subject: [sqlite] UPSERT with multiple constraints Dear all, I have a table with multiple (in this case 2) UNIQUE constraints: UNIQUE (col1, col2) UNIQUE (col1, col3, col4, col5) Is it possible to use UPSERT twice? I have already tried some statements, but neither of these were successful. This is what I want to achieve: INSERT INTO ... ON CONFLICT DO UPDATE SET ... So the update should occur no matter which UNIQUE-constraint would be violated by the insert. I've also tried ON CONFLICT (col1, col2, col3, col4, col5) which is rejected ("does not match any UNIQUE constraint"). The error message is perfectly correct, but doesn't solve my problem ;-) So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT (col1, col3, col4, col5) DO UPDATE". Any hints for me? Kind regards, Thomas _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
