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

[sqlite] UPSERT documentation question

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

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,

[sqlite] Upsert inside trigger?

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

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 =

[sqlite] upsert unique partial index

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)

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

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

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

Re: [sqlite] UPSERT with multiple constraints

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

Re: [sqlite] UPSERT with multiple constraints

@mailinglists.sqlite.org Sent: Friday, March 29, 2019, 17:03:09 Subject: [sqlite] UPSERT with multiple constraints On Wed, 27 Mar 2019 23:59:47 +0100 Thomas Kurz wrote: > Sure. I have a table of items. Each item has a type, a name, and > properties A, B, C (and some more, but they're not re

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:

[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

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

[sqlite] upsert from select

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

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.

[sqlite] Upsert syntax question

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=?)

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 >

[sqlite] UPSERT on constraint name instead of conflict target

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

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

[sqlite] UPSERT fails for unique partial index

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

[sqlite] UPSERT and overlapping UNIQUE indices

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;

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

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

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

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

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

[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

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

[sqlite] UPSERT

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

[sqlite] UPSERT

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,

[sqlite] UPSERT

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)

[sqlite] UPSERT

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

[sqlite] UPSERT

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 an

[sqlite] UPSERT

: 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

[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] UPSERT

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

[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] UPSERT

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.

[sqlite] UPSERT

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

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

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

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

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

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

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

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

[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

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

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

[sqlite] 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 TESTMARY( VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT) and a CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE

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

[sqlite] UPSERT again

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