Re: [sqlite] UPSERT with multiple constraints

[David Raymond]

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

Re: [sqlite] 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 
To: 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  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

Re: [sqlite] UPSERT with multiple constraints

[James K. Lowden]

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

Re: [sqlite] UPSERT with multiple constraints

[Roger Schlueter]

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

Re: [sqlite] UPSERT with multiple constraints

[Simon Slavin]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT with multiple constraints

[Simon Slavin]

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:



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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users