UPSERT with multiple constraints

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

UPSERT with multiple constraints

Thomas Kurz
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

Simon Slavin-3
On 27 Mar 2019, at 2:40pm, Thomas Kurz <[hidden email]> 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:

<https://www.sqlite.org/lang_createtrigger.html>

Unfortunately this just shifts the difficulty to a different kind of statement.

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.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

Thomas Kurz
> 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 certain type and name are unique: UNIQUE (type, name). But there can be multiple items with the same name as long as they are of different types.

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).

I cannot use UNIQUE (type, name, A, B, C), as this would mean that there can be two items with the same A, B, C (and type, of course), but different name. On the other hand, there could be two items with the same same (and type, of course) but different 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.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

Simon Slavin-3
On 27 Mar 2019, at 10:59pm, Thomas Kurz <[hidden email]> 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 separate items would be in their own file and your "items of a certain type that have identical properties" would be constructed by scanning that table with a DISTINCT.

So yes, it's difficult to do what you want in SQL because your data isn't in a form that SQL likes.  I see two options: change your schema, or do your processing in your programming language rather than in SQL.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

Roger Schlueter
In reply to this post by Thomas Kurz
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 whose Name is unique "Gadgets" so your Gadgets
table would be:

GADGETS
{Name, A, B, C, ....}  UNIQUE(Name)

I assume there are other things:

THINGS
{Type, Name, A, B, C, .....}  No(?) uniqueness

Knowing the Type of items to be updated, you know which table to use.

On 3/27/2019 15:59, Thomas Kurz wrote:

>> 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 certain type and name are unique: UNIQUE (type, name). But there can be multiple items with the same name as long as they are of different types.
>
> 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).
>
> I cannot use UNIQUE (type, name, A, B, C), as this would mean that there can be two items with the same A, B, C (and type, of course), but different name. On the other hand, there could be two items with the same same (and type, of course) but different 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.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: UPSERT with multiple constraints

Richard Damon
I think it can all be done in a single table, with columns, A, B, C,
type, Name, and other things, with indexes/constraints
UNIQUE(A, B, C, Type)
UNIQUE(Name, Type)

and quite possible an addition PRIMARY index, perhaps the default ROWID
one, as neither of those UNIQUE indexes look to be ideal as a primary
key for other tables that might want to refer to an item to use.

On 3/28/19 1:59 AM, Roger Schlueter wrote:

> 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 whose Name is unique "Gadgets" so your Gadgets
> table would be:
>
> GADGETS
> {Name, A, B, C, ....}  UNIQUE(Name)
>
> I assume there are other things:
>
> THINGS
> {Type, Name, A, B, C, .....}  No(?) uniqueness
>
> Knowing the Type of items to be updated, you know which table to use.
>
> On 3/27/2019 15:59, Thomas Kurz wrote:
>>> 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 certain type and name are unique:
>> UNIQUE (type, name). But there can be multiple items with the same
>> name as long as they are of different types.
>>
>> 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).
>>
>> I cannot use UNIQUE (type, name, A, B, C), as this would mean that
>> there can be two items with the same A, B, C (and type, of course),
>> but different name. On the other hand, there could be two items with
>> the same same (and type, of course) but different 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.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

James K. Lowden
In reply to this post by Thomas Kurz
On Wed, 27 Mar 2019 23:59:47 +0100
Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

Thomas Kurz
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 <[hidden email]>
To: [hidden email] <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT with multiple constraints

David Raymond
In reply to this post by Thomas Kurz
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users