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
@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 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 re
On Wed, 27 Mar 2019 23:59:47 +0100
Thomas Kurz 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
Following Simons' comment, changing the schema to conform to SQL
expectations would involve having at least two tables. Consider your
second uniqueness criterion; Let's call those items a "Widget" so your
Widget table would be:
WIDGETS
{A, B, C, .} UNIQUE(A,B,C)
Let's call your items
On 27 Mar 2019, at 10:59pm, Thomas Kurz wrote:
> 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.
Mmm. Your database design doesn't work the way SQL does. Your
> Can I ask what it is that you're trying to do ? This smacks of trying to add
> 1 to an existing value or something like that.
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 that items of a
On 27 Mar 2019, at 2:40pm, Thomas Kurz wrote:
> So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT
> (col1, col3, col4, col5) DO UPDATE".
Can't be done in a single instruction. The nearest you can get to this would
involve using triggers:
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
8 matches
Mail list logo
