You are right. This is indeed a situation that I didn't have in mind.
I will rethink the data design. Thank you very much for this comment and also
to all others which gave me valuable ideas on how to handle my conflict.
----- Original Message -----
From: James K. Lowden <jklow...@schemamania.org>
To: sqlite-users@mailinglists.sqlite.org <sqlite-users@mailinglists.sqlite.org>
Sent: Friday, March 29, 2019, 17:03:09
Subject: [sqlite] UPSERT with multiple constraints
On Wed, 27 Mar 2019 23:59:47 +0100
Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
> Sure. I have a table of items. Each item has a type, a name, and
> properties A, B, C (and some more, but they're not relevant here).
> I want to enforce ... UNIQUE (type, name).
...
> Furthermore, items of a certain type that have identical properties
> A, B, C are also considered equal, regardless of their name: UNIQUE
> (type, A, B, C).
...
> Now when inserting an item that already exists (according to the
> uniqueness definition above), the existing item should be updated
> with the new name and A, B, C properties.
IIUC, by "upsert" you mean that for a new row matching an existing row
on {type, A, B, C}, instead of inserting the new row, you want to update
the existing row with the new row's name. Unless, that is, the new row
would then conflict with (i.e., match) a different row on {type, name},
in which case the update fails. If no row matches either criteria, you
want to insert the row.
So why not use SQL to do that as designed, instead of relying on the
strange upsert?
insert into T values ( 'type', 'name', 'a', 'b', 'c' )
where not exists ( select 1 from T
where type = 'type' and name = 'name'
or A = 'a' and B = 'b' and C = 'c'
);
update T set name = 'name'
where A = 'a' and B = 'b' and C = 'c';
For efficiency you can check that the first insert affected zero rows
before updating, but that's not strictly necessary.
If there's a possibility of other processes updating the database
between the two statements, wrap them in a transaction.
--jkl
_______________________________________________
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
