Re: [sqlite] UPSERT documentation question

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

Re: [sqlite] UPSERT documentation question

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

Re: [sqlite] UPSERT documentation question

> 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

Re: [sqlite] Upsert inside trigger?

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

Re: [sqlite] Upsert inside trigger?

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

Re: [sqlite] Upsert inside trigger?

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,

Re: [sqlite] upsert unique partial index

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

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 =

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

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

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

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

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

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

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

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

Re: [sqlite] UPSERT with multiple constraints

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

Re: [sqlite] UPSERT with multiple constraints

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:

Re: [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

Re: [sqlite] UPSERT with multiple constraints

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

Re: [sqlite] UPSERT with multiple constraints

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

Re: [sqlite] UPSERT with multiple constraints

> 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

Re: [sqlite] UPSERT with multiple constraints

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:

Re: [sqlite] upsert from select

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

Re: [sqlite] upsert from select

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

Re: [sqlite] upsert from select

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

Re: [sqlite] Upsert syntax question

rg] 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 u

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.

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

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

Re: [sqlite] UPSERT fails for unique partial index

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 >

Re: [sqlite] UPSERT fails for unique partial index

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

Re: [sqlite] UPSERT fails for unique partial index

> 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

Re: [sqlite] UPSERT fails for unique partial index

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

Re: [sqlite] UPSERT available in pre-release

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

Re: [sqlite] UPSERT available in pre-release

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

Re: [sqlite] UPSERT available in pre-release

Please try again with the latest pre-release snapshot. On 4/19/18, Quan Yong Zhai 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

Re: [sqlite] UPSERT available in pre-release

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:

Re: [sqlite] UPSERT available in pre-release

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

Re: [sqlite] UPSERT available in pre-release

> 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

Re: [sqlite] UPSERT available in pre-release

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

Re: [sqlite] UPSERT available in pre-release

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

Re: [sqlite] UPSERT available in pre-release

> 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

Re: [sqlite] UPSERT

pated 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

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

> 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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT

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

Re: [sqlite] UPSERT again

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

Re: [sqlite] UPSERT again

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

Re: [sqlite] UPSERT again

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

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

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

Re: [sqlite] UPSERT again

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

Re: [sqlite] UPSERT again

[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

Re: [sqlite] UPSERT again

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

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