Re: [sqlite] UPSERT documentation question

[nomad]

I should perhaps point out that the issue has been solved - the page
has been adjusted. Thanks devs.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT documentation question

[nomad]

On Fri Feb 07, 2020 at 01:45:53PM +, David Raymond wrote:
> > CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT
> > 1); INSERT INTO vocabulary(word) VALUES('jovial') ON CONFLICT(word)
> > DO UPDATE SET count=count+1;
> > 
> > Shouldn't that actually be written as "vocabulary.count+1"?
> 
> Nope. Unqualified names there refer to the one and only record that's
> getting updated.

Your edit of my email broke some context. I was actually referring to
the paragraph after the example SQL, where "vocabularly.count" was
given as being equivalent to "count+1".

> Similar to how in an blanket update statement you would do:
> update vocabulary set count = count + 1;
> ...and not:
> update vocabulary set vocabulary.count = vocabulary.count + 1;
> 
> I mean, it might still work, but it's not needed, no.

It is needed if you are have a correllated subquery in the UPDATE
statement and want to refer to the original row.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT documentation question

[David Raymond]

> CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
> INSERT INTO vocabulary(word) VALUES('jovial')
> ON CONFLICT(word) DO UPDATE SET count=count+1;
> 
> Shouldn't that actually be written as "vocabulary.count+1"?

Nope. Unqualified names there refer to the one and only record that's getting 
updated.

Similar to how in an blanket update statement you would do:
update vocabulary set count = count + 1;
...and not:
update vocabulary set vocabulary.count = vocabulary.count + 1;

I mean, it might still work, but it's not needed, no.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT documentation question

[nomad]

The page https://sqlite.org/lang_UPSERT.html includes the following
text:

Some examples will help illustrate the difference:

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;

The upsert above inserts the new vocabulary word "jovial" if that
word is not already in the dictionary, or if it is already in the
dictionary, it increments the counter. The "count+1" expression
could also be written as "vocabulary.count". ...

Shouldn't that actually be written as "vocabulary.count+1"?

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upsert inside trigger?

[Darren Duncan]

On 2019-06-16 6:11 a.m., Adrian Ho wrote:

 From https://sqlite.org/lang_createtrigger.html :

*Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
Triggers*

[...]

   * Common table expression are not supported for statements inside of
 triggers.


I wonder what the reason for this is, since that page doesn't seem to say.  Why 
there are any restrictions at all of this nature in triggers. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upsert inside trigger?

[ingo]

On 16-6-2019 15:11, Adrian Ho wrote:
> Common table expression are not supported for statements inside of
> triggers.

Ah, I searched the docs for 'upsert', 'with' ...
Thanks.

ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upsert inside trigger?

[Adrian Ho]

On 16/6/19 8:37 PM, ingo wrote:
> Upon creation, the trigger below gives a syntax error near INSERT.
> Without the trigger surrounding it, the query works well.
> The docs give me no clue to what goes wrong.

From https://sqlite.org/lang_createtrigger.html :

*Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
Triggers*

The UPDATE , DELETE
, and INSERT
 statements within triggers do not
support the full syntax for UPDATE
, DELETE
, and INSERT
 statements. The following
restrictions apply:

[...]

  * Common table expression are not supported for statements inside of
triggers.

> CREATE TRIGGER IF NOT EXISTS update_balances
> AFTER INSERT ON journal
> BEGIN
> WITH inup(account_id, value_balance, amount_balance) AS (
> --ledgers is a view
>  SELECT ledgers.account_id,
> SUM(ledgers.asset_value),
> SUM(ledgers.asset_amount)
>FROM ledgers
>   WHERE ledgers.account_id = 11
> )
> INSERT INTO balances(account_id, value_balance, amount_balance)
> VALUES (
> (SELECT account_id FROM inup),
> (SELECT value_balance FROM inup),
> (SELECT amount_balance FROM inup)
> )
> ON CONFLICT (balances.account_id)
>   DO UPDATE
> SET value_balance = (SELECT value_balance  FROM inup),
> amount_balance= (SELECT amount_balance FROM inup)
>   WHERE account_id = 11
> ;
> END;
At a minimum, you'll have to factor out that WITH clause.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Upsert inside trigger?

[ingo]

Upon creation, the trigger below gives a syntax error near INSERT.
Without the trigger surrounding it, the query works well.
The docs give me no clue to what goes wrong.

Ingo

---%<--%<--%<---

CREATE TRIGGER IF NOT EXISTS update_balances
AFTER INSERT ON journal
BEGIN
WITH inup(account_id, value_balance, amount_balance) AS (
--ledgers is a view
 SELECT ledgers.account_id,
SUM(ledgers.asset_value),
SUM(ledgers.asset_amount)
   FROM ledgers
  WHERE ledgers.account_id = 11
)
INSERT INTO balances(account_id, value_balance, amount_balance)
VALUES (
(SELECT account_id FROM inup),
(SELECT value_balance FROM inup),
(SELECT amount_balance FROM inup)
)
ON CONFLICT (balances.account_id)
  DO UPDATE
SET value_balance = (SELECT value_balance  FROM inup),
amount_balance= (SELECT amount_balance FROM inup)
  WHERE account_id = 11
;
END;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upsert unique partial index

[ingo]

On 5-6-2019 12:52, Richard Hipp wrote:
> WHERE param='_'

query executed,

thanks,

ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upsert unique partial index

[Richard Hipp]

On 6/5/19, ingo  wrote:
> First it tells me an unique constraint failed and then it can't find it?
>
> CREATE TABLE testupsert (
>id INTEGER NOT NULL,
>param TEXT NOT NULL DEFAULT '_',
>sometxt TEXT
> );
>
> CREATE UNIQUE INDEX up
> ON testupsert (id, param)
> WHERE param = '_';
>
> INSERT INTO testupsert (id, sometxt)
> VALUES (1,'1'), (2,'2');
>
> INSERT INTO testupsert (id, sometxt)
> VALUES (1,'test')
> --as expected
> --Error: UNIQUE constraint failed: testupsert.id, testupsert.param
>
> INSERT INTO testupsert (id, sometxt)
> VALUES (1,'test')
> ON CONFLICT (id, param)

Add here:  WHERE param='_'


> DO UPDATE
> SET param = 'updated';
> --Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE
> constraint
>
> ingo
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] upsert unique partial index

[ingo]

First it tells me an unique constraint failed and then it can't find it?

CREATE TABLE testupsert (
   id INTEGER NOT NULL,
   param TEXT NOT NULL DEFAULT '_',
   sometxt TEXT
);

CREATE UNIQUE INDEX up
ON testupsert (id, param)
WHERE param = '_';

INSERT INTO testupsert (id, sometxt)
VALUES (1,'1'), (2,'2');

INSERT INTO testupsert (id, sometxt)
VALUES (1,'test')
--as expected
--Error: UNIQUE constraint failed: testupsert.id, testupsert.param

INSERT INTO testupsert (id, sometxt)
VALUES (1,'test')
ON CONFLICT (id, param)
DO UPDATE
SET param = 'updated';
--Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE
constraint

ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

[Manuel Rigger]

Great, thanks!

Best,
Manuel

On Thu, May 2, 2019 at 6:25 PM Richard Hipp  wrote:

> Documentation has been updated in an attempt to clarify when UPSERT
> applies and when it does not.
>
> On 5/2/19, Manuel Rigger  wrote:
> > Okay, thanks for the clarification!
> >
> > I think that this part of the documentation is ambiguous. The part of the
> > documentation that you quoted mentions a "conflict target", but there is
> no
> > conflict target in the example that I provided.  The documentation
> > continues by stating that a conflict target is not necessary and that "A
> DO
> > NOTHING upsert without a conflict target works the same as an INSERT OR
> > IGNORE." Would it maybe be helpful to update the documentation to
> > explicitly state that UPSERT does not apply to NOT NULL constraints, and
> > that apart from this case DO NOTHING works in the same way as INSERT OR
> > IGNORE?
> >
> > Best,
> > Manuel
> >
> > On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:
> >
> >> On 5/2/19, Manuel Rigger  wrote:
> >> > Hi everyone,
> >> >
> >> > It seems that upsert does not take into account "NOT NULL"
> constraints.
> >> In
> >> > the example below, I get an error "NOT NULL constraint failed:
> >> > test.c0":
> >> >
> >> > CREATE TABLE test (c0 NOT NULL);
> >> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;
> >> >
> >> > I would have expected that the second statement has the same effect as
> >> the
> >> > following statement, which would also be confirmed by the docs [1]:
> >> >
> >> > INSERT OR IGNORE INTO test(c0) VALUES (NULL);
> >> >
> >> > The example seems to work for the UNIQUE and PRIMARY KEY constraints.
> >>
> >> UPSERT is not standard SQL - it is a PostgreSQL extension that we have
> >> attempted to replicate.  PostgreSQL behaves the same way in this test
> >> (a fact that I have just now verified on sqlfiddle.com).  The ON
> >> CONFLICT clause is only triggered by uniqueness constraints, not NOT
> >> NULL constraints.
> >>
> >> The UPSERT documentation says "The conflict target specifies a
> >> specific uniqueness constraint that will trigger the upsert."  So it
> >> does not explicitly say that UPSERT does not work for NOT NULL
> >> constraints, but that is the implication.
> >>
> >>
> >> --
> >> D. Richard Hipp
> >> d...@sqlite.org
> >>
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

[Richard Hipp]

Documentation has been updated in an attempt to clarify when UPSERT
applies and when it does not.

On 5/2/19, Manuel Rigger  wrote:
> Okay, thanks for the clarification!
>
> I think that this part of the documentation is ambiguous. The part of the
> documentation that you quoted mentions a "conflict target", but there is no
> conflict target in the example that I provided.  The documentation
> continues by stating that a conflict target is not necessary and that "A DO
> NOTHING upsert without a conflict target works the same as an INSERT OR
> IGNORE." Would it maybe be helpful to update the documentation to
> explicitly state that UPSERT does not apply to NOT NULL constraints, and
> that apart from this case DO NOTHING works in the same way as INSERT OR
> IGNORE?
>
> Best,
> Manuel
>
> On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:
>
>> On 5/2/19, Manuel Rigger  wrote:
>> > Hi everyone,
>> >
>> > It seems that upsert does not take into account "NOT NULL" constraints.
>> In
>> > the example below, I get an error "NOT NULL constraint failed:
>> > test.c0":
>> >
>> > CREATE TABLE test (c0 NOT NULL);
>> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;
>> >
>> > I would have expected that the second statement has the same effect as
>> the
>> > following statement, which would also be confirmed by the docs [1]:
>> >
>> > INSERT OR IGNORE INTO test(c0) VALUES (NULL);
>> >
>> > The example seems to work for the UNIQUE and PRIMARY KEY constraints.
>>
>> UPSERT is not standard SQL - it is a PostgreSQL extension that we have
>> attempted to replicate.  PostgreSQL behaves the same way in this test
>> (a fact that I have just now verified on sqlfiddle.com).  The ON
>> CONFLICT clause is only triggered by uniqueness constraints, not NOT
>> NULL constraints.
>>
>> The UPSERT documentation says "The conflict target specifies a
>> specific uniqueness constraint that will trigger the upsert."  So it
>> does not explicitly say that UPSERT does not work for NOT NULL
>> constraints, but that is the implication.
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>>
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

[Manuel Rigger]

Okay, thanks for the clarification!

I think that this part of the documentation is ambiguous. The part of the
documentation that you quoted mentions a "conflict target", but there is no
conflict target in the example that I provided.  The documentation
continues by stating that a conflict target is not necessary and that "A DO
NOTHING upsert without a conflict target works the same as an INSERT OR
IGNORE." Would it maybe be helpful to update the documentation to
explicitly state that UPSERT does not apply to NOT NULL constraints, and
that apart from this case DO NOTHING works in the same way as INSERT OR
IGNORE?

Best,
Manuel

On Thu, May 2, 2019 at 5:38 PM Richard Hipp  wrote:

> On 5/2/19, Manuel Rigger  wrote:
> > Hi everyone,
> >
> > It seems that upsert does not take into account "NOT NULL" constraints.
> In
> > the example below, I get an error "NOT NULL constraint failed: test.c0":
> >
> > CREATE TABLE test (c0 NOT NULL);
> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;
> >
> > I would have expected that the second statement has the same effect as
> the
> > following statement, which would also be confirmed by the docs [1]:
> >
> > INSERT OR IGNORE INTO test(c0) VALUES (NULL);
> >
> > The example seems to work for the UNIQUE and PRIMARY KEY constraints.
>
> UPSERT is not standard SQL - it is a PostgreSQL extension that we have
> attempted to replicate.  PostgreSQL behaves the same way in this test
> (a fact that I have just now verified on sqlfiddle.com).  The ON
> CONFLICT clause is only triggered by uniqueness constraints, not NOT
> NULL constraints.
>
> The UPSERT documentation says "The conflict target specifies a
> specific uniqueness constraint that will trigger the upsert."  So it
> does not explicitly say that UPSERT does not work for NOT NULL
> constraints, but that is the implication.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint

[Richard Hipp]

On 5/2/19, Manuel Rigger  wrote:
> Hi everyone,
>
> It seems that upsert does not take into account "NOT NULL" constraints. In
> the example below, I get an error "NOT NULL constraint failed: test.c0":
>
> CREATE TABLE test (c0 NOT NULL);
> INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;
>
> I would have expected that the second statement has the same effect as the
> following statement, which would also be confirmed by the docs [1]:
>
> INSERT OR IGNORE INTO test(c0) VALUES (NULL);
>
> The example seems to work for the UNIQUE and PRIMARY KEY constraints.

UPSERT is not standard SQL - it is a PostgreSQL extension that we have
attempted to replicate.  PostgreSQL behaves the same way in this test
(a fact that I have just now verified on sqlfiddle.com).  The ON
CONFLICT clause is only triggered by uniqueness constraints, not NOT
NULL constraints.

The UPSERT documentation says "The conflict target specifies a
specific uniqueness constraint that will trigger the upsert."  So it
does not explicitly say that UPSERT does not work for NOT NULL
constraints, but that is the implication.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT clause does not work with "NOT NULL" constraint

[Manuel Rigger]

Hi everyone,

It seems that upsert does not take into account "NOT NULL" constraints. In
the example below, I get an error "NOT NULL constraint failed: test.c0":

CREATE TABLE test (c0 NOT NULL);
INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING;

I would have expected that the second statement has the same effect as the
following statement, which would also be confirmed by the docs [1]:

INSERT OR IGNORE INTO test(c0) VALUES (NULL);

The example seems to work for the UNIQUE and PRIMARY KEY constraints.

Best,
Manuel

[1] "A DO NOTHING upsert without a conflict target works the same as an
INSERT OR IGNORE." at https://sqlite.org/lang_UPSERT.html
___
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

[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

Re: [sqlite] upsert from select

[R Smith]

On 2018/11/30 12:50 PM, Eric Grange wrote:

Apparently adding just a "WHERE 1" clause is enough...


Indeed, glad it works.




PS: I used "wcount" rather because "count" is an internal SQL function.

Indeed, though it seems to be accepted here, I am not using a field with my
name in may actual code.
I only used it because that was in the example I copy-pasted from the SQL
doc, I guess the doc could
be updated (it's in https://sqlite.org/lang_UPSERT.html)


Apologies, I wasn't judging your use of it (or the documentation's), 
just explaining why my example deviated from yours in that regard.


They can and do definitely work in many cases, such as the above, and 
even when they don't work, simply enclosing in double-quotes will fix 
it.  Some people's answer to this is to always use the quotes, my 
approach is to avoid reserved words/functions out of principle - a 
practice I'm prepared to admit is probably the worse of the choices, 
because a word might become reserved over some years of development 
[think WITH, UNLIKELY, DO,  etc.] which may render older SQL using any 
of those: "unsafe", whereas double-quoting is future-proof.


But what can I say? I live dangerously!


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upsert from select

[Eric Grange]

Thanks!

Apparently adding just a "WHERE 1" clause is enough, ie. this passes

INSERT INTO vocabulary(word, count)
SELECT * FROM (SELECT  'jovial', 1) WHERE 1
ON CONFLICT(word) DO UPDATE SET count=count+1


and the "WHERE 1" also makes the query with a json_each pass (not just in
the snippet I posted, but also
in the more complex I am actually using)

> PS: I used "wcount" rather because "count" is an internal SQL function.

Indeed, though it seems to be accepted here, I am not using a field with my
name in may actual code.
I only used it because that was in the example I copy-pasted from the SQL
doc, I guess the doc could
be updated (it's in https://sqlite.org/lang_UPSERT.html)




Le ven. 30 nov. 2018 à 11:05, R Smith  a écrit :

> This does seem like a small bug.
>
> While the SQLite devs are having a look, this Zero-cost work-around
> might suit your needs:
> Simply add a WHERE clause, for example:
>
> CREATE TABLE vocabulary (
>word TEXT NOT NULL PRIMARY KEY,
>wcount INT DEFAULT 1
> );
>
> WITH A(w) AS (
>SELECT 'jovial' UNION ALL
>SELECT 'jovial'
> )
> INSERT INTO vocabulary(word)
> SELECT w FROM A WHERE 1
> ON CONFLICT(word) DO UPDATE SET wcount=wcount+1
> ;
>
>
> SELECT * FROM vocabulary
>
>-- word   |wcount
>-- -- | 
>-- jovial |   2
>
>
> PS: I used "wcount" rather because "count" is an internal SQL function.
>
>
> On 2018/11/30 11:14 AM, Eric Grange wrote:
> > Hi,
> >
> > I am running into a little trouble when trying to use and "upsert" from a
> > select clause.
> >
> > Starting from the "vocabulary" exemple at
> > https://sqlite.org/lang_UPSERT.html this works
> >
> > INSERT INTO vocabulary(word, count)
> > SELECT 'jovial', 1
> > ON CONFLICT(word) DO UPDATE SET count=count+1
> >
> >
> > but as soon as the "SELECT" has a from clause it does not seem to be
> > working (near "DO": syntax error)
> >
> > INSERT INTO vocabulary(word, count)
> > SELECT * FROM (SELECT  'jovial', 1)
> > ON CONFLICT(word) DO UPDATE SET count=count+1
> >
> >
> > (without the ON CONFLICT clause the above is accepted)
> >
> > I have tried to place the SELECT between parenthesis, but SQLite then
> > complains of an error on the opening parenthesis.
> > Any workarounds ?
> >
> > My actual usage case would actually use a json_each to provide the values
> > (in case that throws an extra spanner...), like in
> >
> > INSERT INTO vocabulary (word, count)
> > SELECT atom, 1 from json_each('["alpha","beta"]')
> > ON CONFLICT(word) DO UPDATE SET count=count+1
> >
> >
> > Eric
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upsert from select

[R Smith]

This does seem like a small bug.

While the SQLite devs are having a look, this Zero-cost work-around 
might suit your needs:

Simply add a WHERE clause, for example:

CREATE TABLE vocabulary (
  word TEXT NOT NULL PRIMARY KEY,
  wcount INT DEFAULT 1
);

WITH A(w) AS (
  SELECT 'jovial' UNION ALL
  SELECT 'jovial'
)
INSERT INTO vocabulary(word)
SELECT w FROM A WHERE 1
ON CONFLICT(word) DO UPDATE SET wcount=wcount+1
;


SELECT * FROM vocabulary

  -- word   |    wcount
  -- -- | 
  -- jovial |   2


PS: I used "wcount" rather because "count" is an internal SQL function.


On 2018/11/30 11:14 AM, Eric Grange wrote:

Hi,

I am running into a little trouble when trying to use and "upsert" from a
select clause.

Starting from the "vocabulary" exemple at
https://sqlite.org/lang_UPSERT.html this works

INSERT INTO vocabulary(word, count)
SELECT 'jovial', 1
ON CONFLICT(word) DO UPDATE SET count=count+1


but as soon as the "SELECT" has a from clause it does not seem to be
working (near "DO": syntax error)

INSERT INTO vocabulary(word, count)
SELECT * FROM (SELECT  'jovial', 1)
ON CONFLICT(word) DO UPDATE SET count=count+1


(without the ON CONFLICT clause the above is accepted)

I have tried to place the SELECT between parenthesis, but SQLite then
complains of an error on the opening parenthesis.
Any workarounds ?

My actual usage case would actually use a json_each to provide the values
(in case that throws an extra spanner...), like in

INSERT INTO vocabulary (word, count)
SELECT atom, 1 from json_each('["alpha","beta"]')
ON CONFLICT(word) DO UPDATE SET count=count+1


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

[sqlite] upsert from select

[Eric Grange]

Hi,

I am running into a little trouble when trying to use and "upsert" from a
select clause.

Starting from the "vocabulary" exemple at
https://sqlite.org/lang_UPSERT.html this works

INSERT INTO vocabulary(word, count)
SELECT 'jovial', 1
ON CONFLICT(word) DO UPDATE SET count=count+1


but as soon as the "SELECT" has a from clause it does not seem to be
working (near "DO": syntax error)

INSERT INTO vocabulary(word, count)
SELECT * FROM (SELECT  'jovial', 1)
ON CONFLICT(word) DO UPDATE SET count=count+1


(without the ON CONFLICT clause the above is accepted)

I have tried to place the SELECT between parenthesis, but SQLite then
complains of an error on the opening parenthesis.
Any workarounds ?

My actual usage case would actually use a json_each to provide the values
(in case that throws an extra spanner...), like in

INSERT INTO vocabulary (word, count)
SELECT atom, 1 from json_each('["alpha","beta"]')
ON CONFLICT(word) DO UPDATE SET count=count+1


Eric
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upsert syntax question

[David Raymond]

But isn't that the whole point of the whole UPSERT thing?

https://www.sqlite.org/lang_UPSERT.html

...

Ok, found my problem though
https://www.sqlite.org/lang_insert.html

To avoid a parsing ambiguity, the SELECT statement should always contain a 
WHERE clause, even if that clause is simply "WHERE true", if the upsert-clause 
is present. Without the WHERE clause, the parser does not know if the token 
"ON" is part of a join constraint on the SELECT, or the beginning of the 
upsert-clause.


sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new where 
true on conflict (id) do update set in_new = 1;
QUERY PLAN
|--SCAN TABLE gc_new USING COVERING INDEX idx_gc_new_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 30.066 user 14.757695 sys 2.667617



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, October 19, 2018 12:36 PM
To: SQLite mailing list
Subject: Re: [sqlite] Upsert syntax question

On 19 Oct 2018, at 5:24pm, David Raymond  wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
> conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / 
FAIL / ABORT / ROLLBACK / REPLACE.

<https://sqlite.org/lang_conflict.html>

If you need anything more sophisticated, you might like to make a TRIGGER.

Simon.
___
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 syntax question

[Simon Slavin]

On 19 Oct 2018, at 5:24pm, David Raymond  wrote:

> sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
> conflict (id) do update set in_new = 1;

You can't supply a statement for ON CONFLICT.  It can only trigger IGNORE / 
FAIL / ABORT / ROLLBACK / REPLACE.



If you need anything more sophisticated, you might like to make a TRIGGER.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Upsert syntax question

[David Raymond]

Maybe it's just Friday, but I can't figure out what I'm messing up here. Would 
some kind soul point me to where I'm messing up?

Version 3.25.2

sqlite> create table ids (id int primary key, in_old bool, in_new bool);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.234 user 0.00 sys 0.109201

sqlite> insert into ids (id, in_old) select distinct id, 1 from gc_old;
QUERY PLAN
|--SCAN TABLE gc_old USING COVERING INDEX idx_gc_old_id
`--USE TEMP B-TREE FOR DISTINCT
Run Time: real 18.050 user 10.530067 sys 1.279208

sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on 
conflict (id) do update set in_new = 1;
Run Time: real 0.000 user 0.00 sys 0.00
Error: near "do": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT on constraint name instead of conflict target

[Richard Hipp]

The PostgreSQL documentation recommends against using the
constraint-name syntax.  I decided to encourage their recommendation
by not supporting that feature.

On 8/14/18, Jordan Owens  wrote:
> Hi,
>
> In reviewing the documentation for UPSERT, it does not appear that it fully
> supports the PostgreSQL syntax. PostgreSQL provides an option to use the
> name of a constraint instead of a list of columns or index expressions.
>
> For example:
>
> CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
> CREATE UNIQUE INDEX t1b ON t1(b);
> INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
> SELECT * FROM t1;
> INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
> ON CONFLICT ON CONSTRAINT t1b DO UPDATE SET c=excluded.c;
>
> The last query results in a syntax error. Is it possible for SQLite to
> support this option as well?
>
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT fails for unique partial index

[Jordan Owens]

Awesome, thanks!

On Tue, Aug 14, 2018 at 11:16 AM Richard Hipp  wrote:

> On 8/14/18, Richard Hipp  wrote:
> >  I will strive to bring the behavior of SQLite into
> > alignment with PostgreSQL.
>
> Now fixed on trunk
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT on constraint name instead of conflict target

[Jordan Owens]

Hi,

In reviewing the documentation for UPSERT, it does not appear that it fully
supports the PostgreSQL syntax. PostgreSQL provides an option to use the
name of a constraint instead of a list of columns or index expressions.

For example:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
CREATE UNIQUE INDEX t1b ON t1(b);
INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
SELECT * FROM t1;
INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
ON CONFLICT ON CONSTRAINT t1b DO UPDATE SET c=excluded.c;

The last query results in a syntax error. Is it possible for SQLite to
support this option as well?

Thanks!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT fails for unique partial index

[Richard Hipp]

On 8/14/18, Richard Hipp  wrote:
>  I will strive to bring the behavior of SQLite into
> alignment with PostgreSQL.

Now fixed on trunk

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT fails for unique partial index

[Petite Abeille]

> On Aug 14, 2018, at 12:57 PM, Richard Hipp  wrote:
> 
> I will strive to bring the behavior of SQLite into alignment with PostgreSQL.

If only all this energy was spent on a proper MERGE clause instead… sigh… :D

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT fails for unique partial index

[Richard Hipp]

Simplified test case:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
CREATE UNIQUE INDEX t1b ON t1(b);
INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
SELECT * FROM t1;
INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
ON CONFLICT(b) DO UPDATE SET c=excluded.c;

The second INSERT fails raises a conflict on the primary key in
SQLite, but it works in PostgreSQL, leaving behind a single row with
values 1,2,33,4.  SQLite actually does what I designed it to do, and
what it is documented to do, because that is the way that I understood
PostgreSQL would work, based on my reading of the PostgreSQL
documentation and on test queries.  However, I must have missed
something. I will strive to bring the behavior of SQLite into
alignment with PostgreSQL.

On 8/13/18, Jordan Owens  wrote:
> Hello,
>
> I believe I have discovered a bug with UPSERT when a table has a unique
> partial index. I'm getting a constraint exception instead of the database
> updating the record.
>
> SQLite v3.24.0
>
> *Error message:*
>
> SQLite3::ConstraintException: UNIQUE constraint failed: alarms.id:
> INSERT INTO
> "alarms"("id","device_id","alarm_type","status","metadata","created_at","updated_at")
> VALUES (99,17,1,2,'bar','2018-08-13 21:15:38.394975','2018-08-13
> 21:15:38.394975')
> ON CONFLICT (device_id, alarm_type)
> WHERE status <> 0
> DO UPDATE SET "status"=EXCLUDED."status","updated_at"=EXCLUDED."updated_at"
>
> *Table definition:*
>
> CREATE TABLE "alarms" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> "device_id" integer NOT NULL, "alarm_type" integer NOT NULL, "status"
> integer NOT NULL, "metadata" text, "secret_key" blob, "created_at"
> datetime, "updated_at" datetime)
>
> CREATE UNIQUE INDEX "index_alarms_on_device_id_and_alarm_type" ON "alarms"
> ("device_id", "alarm_type") WHERE status <> 0
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT fails for unique partial index

[Jordan Owens]

Hello,

I believe I have discovered a bug with UPSERT when a table has a unique
partial index. I'm getting a constraint exception instead of the database
updating the record.

SQLite v3.24.0

*Error message:*

SQLite3::ConstraintException: UNIQUE constraint failed: alarms.id:
INSERT INTO
"alarms"("id","device_id","alarm_type","status","metadata","created_at","updated_at")
VALUES (99,17,1,2,'bar','2018-08-13 21:15:38.394975','2018-08-13
21:15:38.394975')
ON CONFLICT (device_id, alarm_type)
WHERE status <> 0
DO UPDATE SET "status"=EXCLUDED."status","updated_at"=EXCLUDED."updated_at"

*Table definition:*

CREATE TABLE "alarms" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"device_id" integer NOT NULL, "alarm_type" integer NOT NULL, "status"
integer NOT NULL, "metadata" text, "secret_key" blob, "created_at"
datetime, "updated_at" datetime)

CREATE UNIQUE INDEX "index_alarms_on_device_id_and_alarm_type" ON "alarms"
("device_id", "alarm_type") WHERE status <> 0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT and overlapping UNIQUE indices

[Cezary H. Noweta]

Hello,

A minor observation: UPSERT makes indices to be strictly and sparingly 
endowed with UNIQUEness. For example: uniqueness + partitioning:


CREATE TABLE t(num UNIQUE, cnt DEFAULT 1);
CREATE UNIQUE INDEX idx_neg ON t(num) WHERE num < 0;
CREATE UNIQUE INDEX idx_pos ON t(num) WHERE num >= 0;

Now ``INSERT INTO t(num) VALUES(10) ON CONFLICT(num) DO UPDATE SET 
cnt=cnt+1;'' fails due to a fact that ``idx_pos'' will not pass. It is a 
frequent and common habit to put ``UNIQUE'' everywhere an entity is 
unique. From SQLite 3.24, above--mentioned, partial indices (and 
everything what is not strictly designed to enforce an uniqueness) 
should be non-UNIQUE ones. IMHO, this fact is worth of recalling in the 
doc of UPSERT clause as the fact can become a source of a confusion.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

Perfect, That’s a big step of SQLite.

I think one of our projects will benefit of the new upsert.

Thanks a lot.



wordcount --all :memory: sqlite3.c

  2.422 wordcount --insert

  2.341 wordcount --insert --without-rowid

  3.610 wordcount --replace

  1.766 wordcount --replace --without-rowid

  1.594 wordcount --upsert

  1.625 wordcount --upsert --without-rowid

  2.171 wordcount --select

  2.281 wordcount --select --without-rowid

  2.423 wordcount --update

  2.391 wordcount --update --without-rowid

  0.375 wordcount --delete

  0.328 wordcount --delete --without-rowid

  0.372 wordcount --query

  0.328 wordcount --query --without-rowid

24.027 wordcount --all



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Richard Hipp <d...@sqlite.org>
Sent: Saturday, April 21, 2018 3:49:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] UPSERT available in pre-release

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <q...@msn.com> wrote:
> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows
> 10
>
>
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Richard Hipp <d...@sqlite.org>
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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 available in pre-release

[Sylvain Pointeau]

I full agree with Petite Abeille.
This upsert is quite limited, we can only insert one row on conflict update
one row.
Even so it is a big improvement versus the insert or replace, this is far
from the merge from the SQL standard where we can insert or update multiple
rows in one query.

I am already super mega happy to think about using this new upsert for
sure, but could you implement the merge from the SQL standard?

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <q...@msn.com> wrote:
> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows
> 10
>
>
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Richard Hipp <d...@sqlite.org>
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

On 4/19/18, Quan Yong Zhai  wrote:
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
>
> Before:
>wordcount --all :memory: sqlite3.c
>   2.406 wordcount --insert
>   2.296 wordcount --insert --without-rowid
>
> After:
> wordcount --all :memory: sqlite3.c
>   1.701 wordcount --insert
>   3.547 wordcount --insert --without-rowid
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.

That's a good testing idea.  Thank you.  I will make a similar change
and investigate the cause of the slowdown, and hopefully fix the
problem.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

Dear Richard,

I modified the wordcount.c in SQLite/test directory, to use the new upsert 
command:

   INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET 
cnt=cnt+1

Before:

   wordcount --all :memory: sqlite3.c

  2.406 wordcount --insert

  2.296 wordcount --insert --without-rowid

After:

wordcount --all :memory: sqlite3.c

  1.701 wordcount --insert

  3.547 wordcount --insert --without-rowid



As you can see, it’s very strangely ,in the table with rowid, the upsert 
improved a lot, but in the table without rowidd, it’s slower than the origin 
sql.





Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Richard Hipp <d...@sqlite.org>
Sent: Thursday, April 19, 2018 6:29:55 PM
To: General Discussion of SQLite Database
Subject: [sqlite] UPSERT available in pre-release

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

--
D. Richard Hipp
d...@sqlite.org
___
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 available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 1:06 PM, Richard Hipp  wrote:
> 
> We are open to adding MERGE INTO at some point in the future.

Excellent! 

>  But the UPSERT syntax is both easier to understand

Debatable.

> and easier to implement,

Possibly.

> and we prefer to follow PostgreSQL syntax whenever possible.  See
> https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
> PostgreSQL's rationale for rejecting MERGE.

Let’s agree to disagree on that long running opinion piece.

MERGE, as per SQL:2003 & SQL:2008 & co. is the way to go.

The situation remind me of the introduction of recursive common table 
expression (CTE) in SQLite, which at first you wanted to implement solely in 
terms of Oracle’s 'CONNECT BY’ syntax, but ultimately saw the benefit of 
embracing the full-fledged CTE syntax instead.

And I’m personally very grateful for that thought process which gifted SQLite a 
kickass CTE implementation. Thanks for that!

Hopefully, the UPSERT vs. MERGE conversation will move that way as well: MERGE 
FTW! :)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

On 4/19/18, Petite Abeille  wrote:
>
>
>> On Apr 19, 2018, at 12:29 PM, Richard Hipp  wrote:
>>
>> The latest pre-release snapshot [1]
>
> Link missing?

[1] https://sqlite.org/download.html

>
>> contains support for UPSERT
>> following the PostgreSQL syntax.
>>  The documentation is still pending.
>> Nevertheless, early feedback is welcomed.  You can respond either to
>> this mailing list, or directly to me.
>
> Postgres UPSERT?!?
>
> Wouldn’t a standard ANSI MERGE be more appropriate?
>

We are open to adding MERGE INTO at some point in the future.  But the
UPSERT syntax is both easier to understand and easier to implement,
and we prefer to follow PostgreSQL syntax whenever possible.  See
https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax for
PostgreSQL's rationale for rejecting MERGE.

MySQL also has UPSERT but no MERGE.  The MySQL UPSERT syntax is
similar, but omits the constraint-target clause following the ON
CONFLICT.  So if there are multiple uniqueness constraints, you never
know which one will receive the UPSERT in MySQL.  That seemed
problematic, so we dropped support for the MySQL syntax during
development.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Shevek]

Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the 
least useful because it's very limited: It can only do a check against a 
constraint, and the cost of evaluating that constraint has to be carried 
by all other statements which mutate the table. Oracle/Teradata MERGE is 
a far more useful semantics because it's defined more like a self-join, 
where the constraint is specified in the statement, not the DBMS.


On 04/19/2018 11:29 AM, Richard Hipp wrote:

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Petite Abeille]

> On Apr 19, 2018, at 12:29 PM, Richard Hipp  wrote:
> 
> The latest pre-release snapshot [1]

Link missing?

> contains support for UPSERT
> following the PostgreSQL syntax.
>  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.

Postgres UPSERT?!?

Wouldn’t a standard ANSI MERGE be more appropriate?

https://en.wikipedia.org/wiki/Merge_(SQL)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT available in pre-release

[Richard Hipp]

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Keith Medcalf]

If you have multiple candidate keys for a single row that match more than one 
row (or the alternate candidate keys match different rows), your application 
should explode immediately!  

There is no need to "decide" which row is the correct one to update, you are 
already in a fatal error situation and need to revisit your database design (it 
is probably insufficiently normalized) and already self-inconsistent and 
suffering update anomalies (or you are treating a pseudo-key as a candidate 
key, which for the purposes of UPDATE or INSERT it is not -- the rowid is a 
pseudo-key -- (one of/any of) the "other" candidate keys in the row is the true 
primary key).

CREATE TABLE foo
(
 idinteger primary key,
 foo_key   text not null unique,
 some_data blob
);


  SAVEPOINT UpdateFoo;
 UPDATE foo 
SET some_data = :some_data 
  WHERE foo_key = :foo_key;
  INSERT OR 
IGNORE INTO foo (foo_key, some_data) 
 VALUES (:foo_key, :some_data);
 SELECT id 
   FROM foo 
  WHERE foo_key = :foo_key;
RELEASE UpdateFoo;


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul
>Sent: Monday, 19 March, 2018 11:08
>To: SQLite mailing list
>Subject: Re: [sqlite] UPSERT
>
>I would suggest using the PostgreSQL way:
>  https://www.postgresql.org/docs/9.5/static/sql-insert.html
>
> INSERT INTO ...
>   ON CONFLICT [()] DO UPDATE
>   SET foo = ... , bar = ... ;
>
>This approach is really cool, because we can specify which key is
>more
>important and discard other conflicts as an error. For example, given
>the following table:
>
>CREATE TABLE foo(
>idINTEGER NOT NULL,
>foo_key   TEXT NOT NULL,
>some_data TEXT,
>
>PRIMARY KEY(id),
>UNIQUE (foo_key)
>);
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
>INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");
>
>If we are performing a query:
>
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
> 
>
>Which record should we update and what columns?
>
>Having the ability to specify a specific column on which the conflict
>is actually an acceptable event lets the developer to make a decision
>how to resolve it:
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
> 
>
>
>19 March 2018, 18:41:34, by "R Smith" <ryansmit...@gmail.com>:
>
>> On 2018/03/19 1:50 PM, Olivier Mascia wrote:
>> >
>> > I don't know what any 'standard' SQL defines about this.
>> > I know that FirebirdSQL (where I came from, before meeting
>SQLite) did/does it this way:
>> >
>> > UPDATE OR INSERT INTO
>> > {tablename | viewname} [()]
>> > VALUES ()
>> > [MATCHING ()]
>> > [RETURNING  [INTO ]]
>>
>> Quite right, and the statement in MSSQL is even more convoluted,
>which,
>> if it was in SQLite like this, would require a dynamically created
>SQL
>> statement that is worse than simply computing an UPDATE and an
>INSERT -
>> which a previous poster already lamented.
>>
>> My suggestion for UPSERT would be the very simple already SQLite-
>like
>> syntax of:
>>
>> INSERT OR UPDATE INTO t (k1, k2, ... , kn,  f1, f2, ... , fn)
>> followed by the usual VALUES clause or SELECT query.
>>
>> Any record found to exist with the exact same value in the Primary
>Key
>> field(s) [ k1 .. kn ] has all other fields (that are NOT Primary
>Key
>> fields) updated to the new values, and if no such record is found,
>the
>> row simply gets inserted.  If the inserted row OR updated values
>cause
>> any other constraint to break, then FAIL hard, the same way (and
>> possibly with the same ON CONFLICT options) as any other single
>INSERT
>> or UPDATE would be subjected to.
>>
>> This is far better than INSERT OR REPLACE since there is no delete,
>and
>> no multiple-row delete on constraint violations.
>> It is simple in terms of converting any current INSERT OR REPLACE
>query
>> to an INSERT OR UPDATE query requires changing 1 word only.
>>
>> Triggers should fire for ON INSERT and ON UPDATE according to
>whatever
>> actually is required during the operation.
>>
>> Adding this has no backward compatibility to break, this did not
>exist
>> before and it is not schema-specific.
>>
>>
>> One possible added refi

Re: [sqlite] UPSERT

[Paul]

I would suggest using the PostgreSQL way: 
  https://www.postgresql.org/docs/9.5/static/sql-insert.html

 INSERT INTO ...
   ON CONFLICT [()] DO UPDATE 
   SET foo = ... , bar = ... ;

This approach is really cool, because we can specify which key is more
important and discard other conflicts as an error. For example, given
the following table:

CREATE TABLE foo(
idINTEGER NOT NULL,
foo_key   TEXT NOT NULL,
some_data TEXT,

PRIMARY KEY(id),
UNIQUE (foo_key)
);

INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");

If we are performing a query:


INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
 

Which record should we update and what columns? 

Having the ability to specify a specific column on which the conflict
is actually an acceptable event lets the developer to make a decision
how to resolve it:

INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
 


19 March 2018, 18:41:34, by "R Smith" :

> On 2018/03/19 1:50 PM, Olivier Mascia wrote:
> >
> > I don't know what any 'standard' SQL defines about this.
> > I know that FirebirdSQL (where I came from, before meeting SQLite) did/does 
> > it this way:
> >
> > UPDATE OR INSERT INTO
> > {tablename | viewname} [()]
> > VALUES ()
> > [MATCHING ()]
> > [RETURNING  [INTO ]]
> 
> Quite right, and the statement in MSSQL is even more convoluted, which, 
> if it was in SQLite like this, would require a dynamically created SQL 
> statement that is worse than simply computing an UPDATE and an INSERT - 
> which a previous poster already lamented.
> 
> My suggestion for UPSERT would be the very simple already SQLite-like 
> syntax of:
> 
> INSERT OR UPDATE INTO t (k1, k2, ... , kn,  f1, f2, ... , fn)
> followed by the usual VALUES clause or SELECT query.
> 
> Any record found to exist with the exact same value in the Primary Key 
> field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key 
> fields) updated to the new values, and if no such record is found, the 
> row simply gets inserted.  If the inserted row OR updated values cause 
> any other constraint to break, then FAIL hard, the same way (and 
> possibly with the same ON CONFLICT options) as any other single INSERT 
> or UPDATE would be subjected to.
> 
> This is far better than INSERT OR REPLACE since there is no delete, and 
> no multiple-row delete on constraint violations.
> It is simple in terms of converting any current INSERT OR REPLACE query 
> to an INSERT OR UPDATE query requires changing 1 word only.
> 
> Triggers should fire for ON INSERT and ON UPDATE according to whatever 
> actually is required during the operation.
> 
> Adding this has no backward compatibility to break, this did not exist 
> before and it is not schema-specific.
> 
> 
> One possible added refinement might be an optional second field-group 
> that should be ignored over-and-above the PK fields during the UPDATE. 
> (During the INSERT of course all fields MUST be added).
> 
> 2 ways this can be done easily:
> 
>   A - Use a separate 2nd prototype group for Non-Updating fields, Like 
> this perhaps:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES 
> (...);  -- This example updates only f3 and f4 if the record already exists.
> 
> I just picked "NOT" as the separator, perhaps "KEEP" gives better 
> clarity (see next example), but any good word would do.
> Primary key fields pk1 and pk2 along with specified non-updating fields 
> f1 and f2 are all ignored during an update, but still used during an 
> insert.
> Adding a PK field to the second set is a no-op as some might like it for 
> legibility. i.e this next query is equivalent to the above:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1, 
> f2) VALUES (...);  -- This example updates only f3 and f4, same as above.
> 
> 
>   B - Use a Marker of sorts for Non-Updating fields, Like this perhaps 
> using the Exclamation mark:
> 
> INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...);  -- 
> Again update only f3 and f4 if the record already exists.
> 
> (Adding the marker to a PK field is a no-op).
> Escaping is not needed since a fieldname starting with the same marker 
> will be in the list of field-names, no ambiguity, and in the case where 
> a set of fields contain fields starting with both one and two markers 
> (for which the programmer should be shot, but let's assume it possible) 
> then the field can simply be enclosed in quotes as is the norm for 
> disambiguation in SQLite. This next example has fields named !f and !!f:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...);  -- Here 
> updating only !!f if the record already exists.
> 
> 
> Personally, I'm partial to option A.
> 
> I know it's a bit of work, but it seems less so than many of the other 
> additions - perhaps let's first have 

Re: [sqlite] UPSERT

[R Smith]

On 2018/03/19 1:50 PM, Olivier Mascia wrote:


I don't know what any 'standard' SQL defines about this.
I know that FirebirdSQL (where I came from, before meeting SQLite) did/does it 
this way:

UPDATE OR INSERT INTO
{tablename | viewname} [()]
VALUES ()
[MATCHING ()]
[RETURNING  [INTO ]]


Quite right, and the statement in MSSQL is even more convoluted, which, 
if it was in SQLite like this, would require a dynamically created SQL 
statement that is worse than simply computing an UPDATE and an INSERT - 
which a previous poster already lamented.


My suggestion for UPSERT would be the very simple already SQLite-like 
syntax of:


INSERT OR UPDATE INTO t (k1, k2, ... , kn,  f1, f2, ... , fn)
followed by the usual VALUES clause or SELECT query.

Any record found to exist with the exact same value in the Primary Key 
field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key 
fields) updated to the new values, and if no such record is found, the 
row simply gets inserted.  If the inserted row OR updated values cause 
any other constraint to break, then FAIL hard, the same way (and 
possibly with the same ON CONFLICT options) as any other single INSERT 
or UPDATE would be subjected to.


This is far better than INSERT OR REPLACE since there is no delete, and 
no multiple-row delete on constraint violations.
It is simple in terms of converting any current INSERT OR REPLACE query 
to an INSERT OR UPDATE query requires changing 1 word only.


Triggers should fire for ON INSERT and ON UPDATE according to whatever 
actually is required during the operation.


Adding this has no backward compatibility to break, this did not exist 
before and it is not schema-specific.



One possible added refinement might be an optional second field-group 
that should be ignored over-and-above the PK fields during the UPDATE. 
(During the INSERT of course all fields MUST be added).


2 ways this can be done easily:

 A - Use a separate 2nd prototype group for Non-Updating fields, Like 
this perhaps:


INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES 
(...);  -- This example updates only f3 and f4 if the record already exists.


I just picked "NOT" as the separator, perhaps "KEEP" gives better 
clarity (see next example), but any good word would do.
Primary key fields pk1 and pk2 along with specified non-updating fields 
f1 and f2 are all ignored during an update, but still used during an 
insert.
Adding a PK field to the second set is a no-op as some might like it for 
legibility. i.e this next query is equivalent to the above:


INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1, 
f2) VALUES (...);  -- This example updates only f3 and f4, same as above.



 B - Use a Marker of sorts for Non-Updating fields, Like this perhaps 
using the Exclamation mark:


INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...);  -- 
Again update only f3 and f4 if the record already exists.


(Adding the marker to a PK field is a no-op).
Escaping is not needed since a fieldname starting with the same marker 
will be in the list of field-names, no ambiguity, and in the case where 
a set of fields contain fields starting with both one and two markers 
(for which the programmer should be shot, but let's assume it possible) 
then the field can simply be enclosed in quotes as is the norm for 
disambiguation in SQLite. This next example has fields named !f and !!f:


INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...);  -- Here 
updating only !!f if the record already exists.



Personally, I'm partial to option A.

I know it's a bit of work, but it seems less so than many of the other 
additions - perhaps let's first have another show-of-hands to see if 
this a real need, but it is asked for here more frequently than any 
other feature (to my perception at least).



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Peter Da Silva]

On 3/19/18, 3:37 AM, "sqlite-users on behalf of Paul" 
 wrote:
> Fort me personally, the most sad thing is an annoyance. Because I have to 
> maintain two almost identical queries and manually tweak strategies.

I almost always generate queries dynamically if they're "almost identical". 
Only one-offs get handcrafted queries.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Olivier Mascia]

> Le 19 mars 2018 à 11:28, R Smith  a écrit :
> 
> On 2018/03/19 10:36 AM, Paul wrote:
>> Fort me personally, the most sad thing is an annoyance. Because I have to
>> maintain two almost identical queries and manually tweak strategies.
> 
> I think that there presents the entire UPSERT argument's only real 
> motivation, because it really is negligible from a Database performance point 
> of view.  Never the less, many a lazy programmer do yearn for it (I don't 
> specifically need it, but I am a lazy programmer too, and probably would use 
> it if it existed!), and it seems trivial to implement db-side.
> 
> We keep going in circles between those of us explaining why it's not really 
> better and those of us who really feel warranted in asking for it.
> 
> I never see a dev speak up on this - Is there any dev thoughts on why this 
> isn't considered yet?
> 
> I mean, do we keep lobbying for it, or is there a reason we should all just 
> drop it (and that we might explain to the next person coming on here and 
> asking for it)?.

I don't know what any 'standard' SQL defines about this.
I know that FirebirdSQL (where I came from, before meeting SQLite) did/does it 
this way:

UPDATE OR INSERT INTO
   {tablename | viewname} [()]
   VALUES ()
   [MATCHING ()]
   [RETURNING  [INTO ]]

Setting aside the RETURNING option and also its INTO clause only meant for 
stored procedures, clearly the syntax UPDATE OR INSERT gets its feet on SQLite 
UPDATE OR ROLLBACK/ABORT/REPLACE/FAIL/IGNORE construction which have absolutely 
not the same kind of semantic attached to them (these are conflict resolutions 
on verb UPDATE).  This probably complicates things significantly, and tend to 
weight on the 'Lite' part of the name 'SQLite'.

I would have like to have it in SQLite at some point in past time (would have 
made the transition easier), though learned easily to live without it. :)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[R Smith]

On 2018/03/19 10:36 AM, Paul wrote:

Fort me personally, the most sad thing is an annoyance. Because I have to
maintain two almost identical queries and manually tweak strategies.


I think that there presents the entire UPSERT argument's only real 
motivation, because it really is negligible from a Database performance 
point of view.  Never the less, many a lazy programmer do yearn for it 
(I don't specifically need it, but I am a lazy programmer too, and 
probably would use it if it existed!), and it seems trivial to implement 
db-side.


We keep going in circles between those of us explaining why it's not 
really better and those of us who really feel warranted in asking for it.


I never see a dev speak up on this - Is there any dev thoughts on why 
this isn't considered yet?


I mean, do we keep lobbying for it, or is there a reason we should all 
just drop it (and that we might explain to the next person coming on 
here and asking for it)?.



Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Paul]

19 March 2018, 09:26:15, by "Rowan Worth" :

> On 16 March 2018 at 21:44, Paul <> de...@ukr.net> wrote:
> 
> > A few years back I've been asking the same question. To be honest, there's
> > no more
> > efficient alternative, than the one that can be implemented within library
> > itself.
> > Both performance-wise and productivity-wise.
> >
> > Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with
> > problems:
> >  * Which strategy to choose, INSERT + UPDATE or the reverse? No way to
> > make this generic without hurting performance
> >  * No matter the strategy, we end up with two queries which leads to extra
> > code that has to be maintained and kept in sync plus a penalty from
> > preparing two statements
> >  * Existence of two statements leaves us vulnerable to race conditions,
> > which adds two extra statements to BEGIN and COMMIT a transaction
> >
> 
> I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates
> two statements because in the standard mode of operation (ie. autocommit)
> you're essentially doing:
> 
> (implicit) BEGIN
> INSERT ...
> (implicit) COMMIT
> (implicit) BEGIN
> UPDATE ...
> (implicit) COMMIT
> 
> By making the BEGIN/COMMIT explicit you reduce the overall work when two
> statements are required.

I agree with you here. But then again you have to issue both BEGIN and COMMIT 
through SQL statements, ie through the parser. Even if overhead is small, it's
still present. It all depends on the scenario that database is used in.
If you have a lot of scenarios when you INSERT or UPDATE some data quite 
frequently
then overhead is visible. By manually tweaking strategies in various places 
(whether to use UPDATE first or INSERT) I've managed to improve performance
by tens of percents. Then again, it depends on scenario. Users that use this
model not very often will definitely not benefit that much from UPSERT.
But the ability to work around, and potentially small benefit to an average
user should not be the arguments to dismiss its implementation. After all, 
half of the features in SQLite3 are not that useful to an average user.

> 
> It does seem like sqlite could avoid an extra btree lookup if it
> implemented UPSERT itself, but since the required pages are practically
> guaranteed to be in cache for the second query I wonder how many rows you'd
> need in a table for it to make a significant difference. As you say the
> main benefit would be to avoid synthesising two statements in user code.

It's easy to calculate, exactly twice as much time as it takes to do a B-Tree
lookup. How big is the piece of the pie, again, depends on the scenario.

Fort me personally, the most sad thing is an annoyance. Because I have to
maintain two almost identical queries and manually tweak strategies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Rowan Worth]

On 16 March 2018 at 21:44, Paul  wrote:

> A few years back I've been asking the same question. To be honest, there's
> no more
> efficient alternative, than the one that can be implemented within library
> itself.
> Both performance-wise and productivity-wise.
>
> Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with
> problems:
>  * Which strategy to choose, INSERT + UPDATE or the reverse? No way to
> make this generic without hurting performance
>  * No matter the strategy, we end up with two queries which leads to extra
> code that has to be maintained and kept in sync plus a penalty from
> preparing two statements
>  * Existence of two statements leaves us vulnerable to race conditions,
> which adds two extra statements to BEGIN and COMMIT a transaction
>

I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates
two statements because in the standard mode of operation (ie. autocommit)
you're essentially doing:

(implicit) BEGIN
INSERT ...
(implicit) COMMIT
(implicit) BEGIN
UPDATE ...
(implicit) COMMIT

By making the BEGIN/COMMIT explicit you reduce the overall work when two
statements are required.

It does seem like sqlite could avoid an extra btree lookup if it
implemented UPSERT itself, but since the required pages are practically
guaranteed to be in cache for the second query I wonder how many rows you'd
need in a table for it to make a significant difference. As you say the
main benefit would be to avoid synthesising two statements in user code.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Paul]

A few years back I've been asking the same question. To be honest, there's no 
more  
efficient alternative, than the one that can be implemented within library 
itself. 
Both performance-wise and productivity-wise.

Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with problems:
 * Which strategy to choose, INSERT + UPDATE or the reverse? No way to make 
this generic without hurting performance
 * No matter the strategy, we end up with two queries which leads to extra code 
that has to be maintained and kept in sync plus a penalty from preparing two 
statements
 * Existence of two statements leaves us vulnerable to race conditions, which 
adds two extra statements to BEGIN and COMMIT a transaction

Even if for some reason we dismiss all of the said above, UPSERT scenario is 
wy to common.
Society wise it's much more efficient to make a change in the library for a 
convenience of
thousands (or millions?) of library users rather than leaving it up for them to 
figure out.


Best regards,
Paul

16 March 2018, 12:25:06, by "Robert M. Münch" :

> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since it 
> doesn’t has an UPSERT?
> 
> So, if I have a table with 30 columns and my code updates sub-sets out of 
> these columns, I don’t want to write queries that manually retrieve the old 
> values one by one.
> 
> insert or replace into Book (ID, Name, TypeID, Level, Seen) values ( (select 
> ID from Book where Name = "SearchName"),
>"SearchName",
> 5,
> 6,
> (select Seen from Book where Name = "SearchName"));
> 
> So, for every column I don’t want to change I need to add a sub-select 
> statement. If I need to build this statement dynamically, IMO it would be 
> better to handle this code directly in code:
> 
> if(record-exists?){ UPDATE …
> } else { INSERT …
> }
> 
> Any suggestions / feedback?
> 
> Viele Grüsse.
> 
> -- 
> 
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
> 
> Saphirion AG
> smarter | better | faster
> 
> http://www.saphirion.comhttp://www.nlpp.ch
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.orghttp://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

[Robert M. Münch]

On 16 Mar 2018, at 11:41, Rowan Worth wrote:

> Doing it with an if means you always run two queries - the first to
> determine whether a row exists:
>
> SELECT EXISTS (SELECT ID from Book where Name = "SearchName")

Hi, yes that’s true. Not critical in my case but if you have a slow FFI in 
between this is not ideal.

> There's two approaches which reduce the best case to a single query:
>
> 1. Immediately try to INSERT using the ON ABORT conflict strategy (ie.the
> default). If the query fails with SQLITE_CONSTRAINT you know the row is
> already present, so run the UPDATE.

Yes. In my case we crash hard on any SQLite problems as such a constraint 
violation shouldn’t happen at all and if indicates an invalid application state.

> 2. Immediately try to UPDATE the existing row. Then call sqlite3_changes to
> determine how many rows were updated - if zero then you know the row didn't
> exist, so run the INSERT.

That’s a nice one. I’m going to try this. At least you don’t run two queries 
but you need two calls, replacing the query for the #of-rows-changed query, 
which should be quicker.

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Simon Slavin]

On 16 Mar 2018, at 12:43pm, Robert M. Münch  wrote:

> On 16 Mar 2018, at 11:35, Simon Slavin wrote:
> 
>> You can use INSERT OR IGNORE for this.  First, do an INSERT OR IGNORE 
>> command which will add a dummy entry, with the right key, if one doesn't 
>> already exist.
>> …
> 
> Hi, yes, I thought about this too but really don’t like it and this assumes 
> that you don’t use AUTOINCREMENT on a table otherwise this strategy will fail.

There's no problem with AUTOINCREMENT.  Your UPDATE command should be using 
your own key values, not a rowid generated automatically.

But yes, if you have other objections against using INSERT OR IGNORE then there 
are alternatives.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Robert M. Münch]

On 16 Mar 2018, at 11:35, Simon Slavin wrote:

> You can use INSERT OR IGNORE for this.  First, do an INSERT OR IGNORE command 
> which will add a dummy entry, with the right key, if one doesn't already 
> exist.
> …

Hi, yes, I thought about this too but really don’t like it and this assumes 
that you don’t use AUTOINCREMENT on a table otherwise this strategy will fail.

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Olivier Mascia]

> Le 16 mars 2018 à 11:35, Simon Slavin  a écrit :
> 
>> for every column I don’t want to change I need to add a sub-select 
>> statement. If I need to build this statement dynamically, IMO it would be 
>> better to handle this code directly in code:
>> 
>> if(record-exists?){
>>  UPDATE …
>> } else {
>>  INSERT …
>> }
> 
> You can use INSERT OR IGNORE for this.  First, do an INSERT OR IGNORE command 
> which will add a dummy entry, with the right key, if one doesn't already 
> exist.  Then use an UPDATE command to update the row which now definitely 
> exists.  If you wrap the two commands in a transaction then even if you get 
> power-failure or crash, you will never get a dummy row with no data in the 
> database:
> 
>BEGIN
>INSERT OR IGNORE 
>UPDATE 
>COMMIT

I often do so, slightly differently, passing in all the appropriate values at 
the INSERT OR IGNORE stage, and testing its outcome through sqlite3_changes() 
to learn if the insert did insert or ignore. Allows to run the update only if 
the insert did nothing.

SQLite is an engine in a library and not a black-box-server to which you could 
only "talk" through pure SQL, so I always like to think of the database 
processing as a collaborative merge of the host code and SQLite features.

The syntax UPDATE table set (X,Y,Z) = (V1,V2,V3) greatly helps in helper layers 
to prepare the UPDATE in a syntactic way close to the INSERT INTO table(X,Y,Z) 
values(V1,V2,V3). Only have to account for the WHERE clause of the UPDATE.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Rowan Worth]

On 16 March 2018 at 18:24, Robert M. Münch 
wrote:

> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since
> it doesn’t has an UPSERT?
>
> So, if I have a table with 30 columns and my code updates sub-sets out of
> these columns, I don’t want to write queries that manually retrieve the old
> values one by one.
>
> insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
> (select ID from Book where Name = "SearchName"),
>"SearchName",
> 5,
> 6,
> (select Seen from Book where Name = "SearchName"));
>
> So, for every column I don’t want to change I need to add a sub-select
> statement. If I need to build this statement dynamically, IMO it would be
> better to handle this code directly in code:
>
> if(record-exists?){
> UPDATE …
> } else {
> INSERT …
> }
>

Doing it with an if means you always run two queries - the first to
determine whether a row exists:

SELECT EXISTS (SELECT ID from Book where Name = "SearchName")

There's two approaches which reduce the best case to a single query:

1. Immediately try to INSERT using the ON ABORT conflict strategy (ie.the
default). If the query fails with SQLITE_CONSTRAINT you know the row is
already present, so run the UPDATE.
2. Immediately try to UPDATE the existing row. Then call sqlite3_changes to
determine how many rows were updated - if zero then you know the row didn't
exist, so run the INSERT.

Whether this makes a significant difference in practice I don't know :)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Simon Slavin]

On 16 Mar 2018, at 10:24am, Robert M. Münch  wrote:

> for every column I don’t want to change I need to add a sub-select statement. 
> If I need to build this statement dynamically, IMO it would be better to 
> handle this code directly in code:
> 
> if(record-exists?){
>   UPDATE …
> } else {
>   INSERT …
> }

You can use INSERT OR IGNORE for this.  First, do an INSERT OR IGNORE command 
which will add a dummy entry, with the right key, if one doesn't already exist. 
 Then use an UPDATE command to update the row which now definitely exists.  If 
you wrap the two commands in a transaction then even if you get power-failure 
or crash, you will never get a dummy row with no data in the database:

BEGIN
INSERT OR IGNORE 
UPDATE 
COMMIT

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT

[Robert M. Münch]

Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since it 
doesn’t has an UPSERT?

So, if I have a table with 30 columns and my code updates sub-sets out of these 
columns, I don’t want to write queries that manually retrieve the old values 
one by one.

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
(select ID from Book where Name = "SearchName"),
   "SearchName",
5,
6,
(select Seen from Book where Name = "SearchName"));

So, for every column I don’t want to change I need to add a sub-select 
statement. If I need to build this statement dynamically, IMO it would be 
better to handle this code directly in code:

if(record-exists?){
UPDATE …
} else {
INSERT …
}

Any suggestions / feedback?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT

[chromedou...@yahoo.com]

A proper UPSERT command would be very useful. Here is the simple ON DELETE 
example showing the problem using the single REPLACE command, even with 
deferred foreign keys.


CREATE TABLE a(
id_a INTEGER PRIMARY KEY AUTOINCREMENT,
data_a);

CREATE TABLE b(
id_b INTEGER PRIMARY KEY AUTOINCREMENT,
id_a REFERENCES a ON DELETE CASCADE,
data_b);

INSERT INTO a(data_a) VALUES('123'),('456'),('789');
INSERT INTO b(id_a,data_b) VALUES(1,'XXX'),(2,'YYY'),(3,'ZZZ');

PRAGMA foreign_keys = on;
PRAGMA defer_foreign_keys = on;

BEGIN;
REPLACE INTO a VALUES (2,'654');
END;

SELECT rowid,* FROM a;
SELECT rowid,* FROM b;


1|1|123
2|2|654
3|3|789
1|1|1|XXX
3|3|3|ZZZ

On Wednesday, January 20, 2016 7:29 AM, Hick Gunter  wrote:
>REPLACE will delete the offending row in the referenced table and insert a new 
>one with a newly >generated rowid. If the foreign key references the rowid, 
>the DELETE CASCADE will be invoked because >there is no longer a record with 
>the referenced rowid.

[sqlite] UPSERT

[Paul]

What is the reason to pay extra overhead if it can be omitted?
And actually, SQLite does a great job with concurrency, from my experience. 
There's really smart locking model employed.

And this is really tedious to work around:
  1) You cannot disable FK constraints inside a transaction
  2) Would be really really silly to do so, they're here for a reason, and 
disabling them, even temporarily, is shooting yourself in a foot, sooner or 
later
  3) Two queries means two primary key searches, so for a big tables it's close 
to 100% overhead, unnecessary
  4) It's really bad to maintain two queries, INSERT and UPDATE, in your code, 
good place for bugs to creep in

So the question is completely legitimate. What's re reason it was not 
implemented. 
And should we expect it to be born in some near future? Would be superb awesome.

20 January 2016, 12:50:23, by "Clemens Ladisch" :

>   chromedout64 at yahoo.com wrote:
> > Will SQLite ever get an UPSERT command similar to other DBMS?
> 
> These other DMBSes use a client/server architecture and want to achieve
> high concurrency.
> 
> SQLite has no client/server communication overhead, so there is no large
> downside to using two SQL statements instead.  Furthermore, SQLite has
> no write concurrency, so it does not need an optimized UPSERT locking
> implementation that avoids interfering with other writers.
> 
> That said, I'd guess SQLite will get UPSERT if a paying customer wants
> it.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT

[R Smith]

I think you could also just defer foreign key checking to the end of the 
transaction.


On 2016/01/20 12:20 PM, chromedout64 at yahoo.com wrote:
> The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing 
> rows that are causing the constraint violation prior to inserting or updating 
> the current row"
>
> When using REPLACE with a table containing a foreign key clause ON DELETE 
> CASCADE causes data in other tables to be deleted.
>
> Postgres recently implemented the UPSERT command in 9.5. I was just wondering 
> if SQLite will decide to implement it or not, or if there is some reason they 
> have chosen not to.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT

[Vladimir Vissoultchev]

https://www.sqlite.org/lang_replace.html

Time for new compatibility keyword UPSERT?

cheers,


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Benoit
Mortgat
Sent: Wednesday, January 20, 2016 12:00 PM
To: SQLite mailing list 
Subject: Re: [sqlite] UPSERT

Hello,

It already has, it is called INSERT OR REPLACE
https://www.sqlite.org/lang_insert.html



2016-01-20 2:40 GMT+01:00 :

> Will SQLite ever get an UPSERT command similar to other DBMS? Thanks 
> for any info.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Benoit Mortgat
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT

[Hick Gunter]

REPLACE will delete the offending row in the referenced table and insert a new 
one with a newly generated rowid. If the foreign key references the rowid, the 
DELETE CASCADE will be invoked because there is no longer a record with the 
referenced rowid.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von 
chromedout64 at yahoo.com
Gesendet: Mittwoch, 20. J?nner 2016 13:13
An: SQLite mailing list
Betreff: Re: [sqlite] UPSERT

 Thanks for the info so far.

I attempted to set the PRAGMA defer_foreign_keys = on for the transaction, but 
a REPLACE still deletes data in another table with the ON DELETE CASCADE clause.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] UPSERT

[chromedou...@yahoo.com]

 Thanks for the info so far.

I attempted to set the PRAGMA defer_foreign_keys = on for the transaction, but 
a REPLACE still deletes data in another table with the ON DELETE CASCADE clause.

[sqlite] UPSERT

[Clemens Ladisch]

chromedout64 at yahoo.com wrote:
> Will SQLite ever get an UPSERT command similar to other DBMS?

These other DMBSes use a client/server architecture and want to achieve
high concurrency.

SQLite has no client/server communication overhead, so there is no large
downside to using two SQL statements instead.  Furthermore, SQLite has
no write concurrency, so it does not need an optimized UPSERT locking
implementation that avoids interfering with other writers.

That said, I'd guess SQLite will get UPSERT if a paying customer wants
it.


Regards,
Clemens

[sqlite] UPSERT

[Benoit Mortgat]

Hello,

It already has, it is called INSERT OR REPLACE
https://www.sqlite.org/lang_insert.html



2016-01-20 2:40 GMT+01:00 :

> Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for
> any info.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Benoit Mortgat

[sqlite] UPSERT

[chromedou...@yahoo.com]

The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing rows 
that are causing the constraint violation prior to inserting or updating the 
current row"

When using REPLACE with a table containing a?foreign key clause ON DELETE 
CASCADE causes data in other tables to be deleted.

Postgres recently implemented the UPSERT command in 9.5. I was just wondering 
if SQLite will decide to implement it or not, or if there is some reason they 
have chosen not to.

[sqlite] UPSERT

[chromedou...@yahoo.com]

Will SQLite ever get an UPSERT command similar to other DBMS? Thanks for any 
info.

Re: [sqlite] [FINAL EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE? Thank you

[Frank Chang]

Good evening Michael Black and Igor Tandetnik and sqlite-users group[FINAL
EDIT],
May I ask what is the reason for SQLITE UPSERT performance improvement
with UNIQUE
ON CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [LAST EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good afternoon,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Igor Tandetnik]

On 3/20/2013 9:21 AM, Michael Black wrote:

I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2


That's because there are two (redundant) indexes on the same column that 
need to be updated - one explicitly created, and another implicit in 
UNIQUE constraint.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Michael Black]

29Integer148 000
30SCopy  4 15000
31SCopy  3 16000
32MakeRecord 152 1 ad 00
33SCopy  3 12000
34IsUnique   2 431215 00
35NotExists  1 4312   00
36Rowid  1 16000
37Column 1 0 15   00
38IdxDelete  2 15200
39Rowid  1 16000
40Column 1 0 15   00
41IdxDelete  3 15200
42Delete 1 0 000
43SCopy  4 15000
44SCopy  3 16000
45MakeRecord 152 2 ad 00
46SCopy  3 12000
47IsUnique   3 561215 00
48NotExists  1 5612   00
49Rowid  1 16000
50Column 1 0 15   00
51IdxDelete  2 15200
52Rowid  1 16000
53Column 1 0 15   00
54IdxDelete  3 15200
55Delete 1 0 000
56IdxInsert  3 2 000
57IdxInsert  2 1 000
58MakeRecord 4 5 12aeadd  00
59Insert 1 123 FREQMARY2  0b
60Close  1 0 000
61Close  2 0 000
62Close  3 0 000
63Halt   0 0 000
64Transaction0 1 000
65VerifyCookie   0 4 000
66TableLock  0 4 1 FREQMARY2  00
67Goto   0 2 000

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang
Sent: Wednesday, March 20, 2013 8:01 AM
To: sqlite-users@sqlite.org
Cc: m...@melissadata.com
Subject: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance
improvment with UNIQUE ON CONFLICT IGNORE?

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Igor Tandetnik]

On 3/20/2013 8:29 AM, Frank Chang wrote:

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE). In test #1, we use the
following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)


What's the relationship between TESTMARY and FREQMARY? Why do you show 
the schema of one, when you are concerned with inserts into the other?



With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.


Check whether OR REPLACE part actually works in the second case - 
whether Count gets incremented as expected. I suspect that, with ON 
CONFLICT IGNORE clause, conflicts may in fact be ignored. Then it works 
much faster simply because it makes much fewer writes.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Frank Chang]

Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE). In test #1, we use the
following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE TESTMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Black, Michael (IS)]

Come to thihk of it just add another field which is set to the "old" value 
during the update.

Then both fields are available AFTER INSERT.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:48 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPSERT again

There aren't any true updates, all updates are done using the INSERT OR REPLACE.




 From: jr <creature.eter...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Thursday, January 12, 2012 5:47 PM
Subject: Re: [sqlite] UPSERT again

On 13/01/12 00:17, Tony Caras wrote:
> UPSERT (really means update the record if it exists otherwise insert the 
> record)
>
>
> AFTER INSERT (I meant a trigger after an insert.  In this case I have access
> to the new value but not the "old" values in the record.)
>
> If UPDATE would insert the record if it didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Simon Slavin]

On 13 Jan 2012, at 12:42am, Tony Caras wrote:

> No, the insert I referred to actually is an INSERT OR REPLACE, but it doesn't 
> work because a REPLACE really is a DELETE the original and INSERT the new.  
> So in the example if the blocked field was true in the original record and 
> true in the replacement record, then the counter will get incremented twice 
> (once for the original insert and again for the insert associated with the 
> replace).

INSERT OR REPLACE does correctly trigger the DELETE triggers if it deletes a 
row.  If you implement triggers on INSERT, DELETE and UPDATE everything will 
work correctly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Black, Michael (IS)]

What if you do a BEFORE INSERT and stick the values in a temporary table (use a 
matching rowid).

Then you can just retrieve in in your AFTER trigger.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:48 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPSERT again

There aren't any true updates, all updates are done using the INSERT OR REPLACE.




 From: jr <creature.eter...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Thursday, January 12, 2012 5:47 PM
Subject: Re: [sqlite] UPSERT again

On 13/01/12 00:17, Tony Caras wrote:
> UPSERT (really means update the record if it exists otherwise insert the 
> record)
>
>
> AFTER INSERT (I meant a trigger after an insert.  In this case I have access
> to the new value but not the "old" values in the record.)
>
> If UPDATE would insert the record if it didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Tony Caras]

There aren't any true updates, all updates are done using the INSERT OR REPLACE.




 From: jr <creature.eter...@gmail.com>
To: sqlite-users@sqlite.org 
Sent: Thursday, January 12, 2012 5:47 PM
Subject: Re: [sqlite] UPSERT again
 
On 13/01/12 00:17, Tony Caras wrote:
> UPSERT (really means update the record if it exists otherwise insert the 
> record)
>
>
> AFTER INSERT (I meant a trigger after an insert.  In this case I have access
> to the new value but not the "old" values in the record.)
>
> If UPDATE would insert the record if it didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[jr]

On 13/01/12 00:17, Tony Caras wrote:

UPSERT (really means update the record if it exists otherwise insert the record)


AFTER INSERT (I meant a trigger after an insert.  In this case I have access
to the new value but not the "old" values in the record.)

If UPDATE would insert the record if it didn't exist then I could use the 
trigger you have suggested.




two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Tony Caras]

No, the insert I referred to actually is an INSERT OR REPLACE, but it doesn't 
work because a REPLACE really is a DELETE the original and INSERT the new.  So 
in the example if the blocked field was true in the original record and true in 
the replacement record, then the counter will get incremented twice (once for 
the original insert and again for the insert associated with the replace).




 From: "Black, Michael (IS)" <michael.bla...@ngc.com>
To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org> 
Sent: Thursday, January 12, 2012 5:37 PM
Subject: Re: [sqlite] UPSERT again
 

 
Does REPLACE do what you want?
http://www.sqlite.org/lang_replace.html
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


 
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:17 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPSERT again

UPSERT (really means update the record if it exists otherwise insert the record)


AFTER INSERT (I meant a trigger after an insert.  In this case I have access 
to the new value but not the "old" values in the record.)

If UPDATE would insert the record if it didn't exist then I could use the 
trigger you have suggested.





 From: Simon Slavin <slav...@bigfraud.org>
To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org> 
Sent: Thursday, January 12, 2012 4:40 PM
Subject: Re: [sqlite] UPSERT again
 

On 12 Jan 2012, at 11:20pm, Tony Caras wrote:

> The problem is, that if I create AFTER INSERT then I don't know what the 
> original state of the blocked field was so I don't whether to increment or 
> decrement  or leave the counter alone.

I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed
 by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
    UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
    UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Black, Michael (IS)]

Does REPLACE do what you want?

http://www.sqlite.org/lang_replace.html



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:17 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPSERT again

UPSERT (really means update the record if it exists otherwise insert the record)


AFTER INSERT (I meant a trigger after an insert.  In this case I have access
to the new value but not the "old" values in the record.)

If UPDATE would insert the record if it didn't exist then I could use the 
trigger you have suggested.





 From: Simon Slavin <slav...@bigfraud.org>
To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org>
Sent: Thursday, January 12, 2012 4:40 PM
Subject: Re: [sqlite] UPSERT again


On 12 Jan 2012, at 11:20pm, Tony Caras wrote:

> The problem is, that if I create AFTER INSERT then I don't know what the 
> original state of the blocked field was so I don't whether to increment or 
> decrement  or leave the counter alone.

I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Tony Caras]

UPSERT (really means update the record if it exists otherwise insert the record)


AFTER INSERT (I meant a trigger after an insert.  In this case I have access 
to the new value but not the "old" values in the record.)

If UPDATE would insert the record if it didn't exist then I could use the 
trigger you have suggested.





 From: Simon Slavin <slav...@bigfraud.org>
To: Tony Caras <tca...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org> 
Sent: Thursday, January 12, 2012 4:40 PM
Subject: Re: [sqlite] UPSERT again
 

On 12 Jan 2012, at 11:20pm, Tony Caras wrote:

> The problem is, that if I create AFTER INSERT then I don't know what the 
> original state of the blocked field was so I don't whether to increment or 
> decrement  or leave the counter alone.

I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
    UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
    UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Simon Slavin]

On 12 Jan 2012, at 11:20pm, Tony Caras wrote:

> The problem is, that if I create AFTER INSERT then I don't know what the 
> original state of the blocked field was so I don't whether to increment or 
> decrement  or leave the counter alone.

I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT again

[Tony Caras]

I know there have been discussions in the past about workarounds for UPSERT but 
I recently encountered another one and I haven't figure out a good workaround.  
Any assistance would be greatly appreciated.

I have a situation where I need to increment a counter in another table every 
time a record is added or specific field is modified to a certain value.  For 
example same my record is message and it has a field called blocked.  The 
record can be inserted with blocked field set to true or false.  The fields in 
the  record may get changed/updated many times but if the blocked field gets 
changed to true I need to increment a counter (conversely if it goes from true 
to false i need to decrement the counter).

I can create a trigger that will increment and decrement the counter but the 
record changes are currently done doing INSERT OR REPLACE so record will get 
added if it doesn't exist.  The problem is, that if I create AFTER INSERT then 
I don't know what the original state of the blocked field was so I don't 
whether to increment or decrement  or leave the counter alone.  If I just 
increment when blocked=true then multiple inserts with the same value will 
cause my counter to increment multiple times for the same record. This isn't 
what I need.


Does anyone have any suggestions?  I tried some of the UPSERT type of 
workarounds but so far they haven't worked for me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users