Re: [sqlite] request for feature: insert or update
On Thu, 27 Jun 2013 12:05:00 +0200 Patrik Nilsson wrote: > Now I'm doing: "insert or ignore into table_test (id) values (1)" and > then issue an update statement. I think "insert or update" will > increase performance. The "insert or update" statement is an unnecessary complication. It adds no expressive power to the language; the effect is readily expressed today with an update statement followed by an insert. Performance is not a reason to modify the grammar. It's simply up to the implementation to divine the shortest physical path to the logically expressed outcome. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
I believe the performance of my current statements is better and clearer. >>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and >>> then issue an update statement. I think "insert or update" will >>> increase performance. Although I'm not happy with it. /Patrik On 06/27/2013 03:08 PM, Hick Gunter wrote: > Use parameters and bind... > > REPLACE INTO table_test > SELECT :key, a, b, :val FROM table_test WHERE id=:key > UNION ALL > SELECT :key, NULL, NULL, :val > LIMIT 1; > > -Ursprüngliche Nachricht- > Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] > Gesendet: Donnerstag, 27. Juni 2013 15:01 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] request for feature: insert or update > > Thank you for your suggestion! > > I would like to have all in one statement, then "insert or update" is > perfect. This is least error prone. If you split the statement into several, > you will end up in more statements to test and more bugs. (You write "3" on > two places.) > > Working with blobs, I expect this procedure to waste more time. > >> REPLACE INTO table_test >>SELECT 1, a, b, 3 FROM table_test WHERE id=1 >>UNION ALL >>SELECT 1, NULL, NULL, 3 >>LIMIT 1; > > /Patrik > > On 06/27/2013 02:46 PM, Richard Hipp wrote: >> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson >> wrote: >> >>> Hi All! >>> >>> A feature I'm missing is a syntax like with "insert or update". >>> >>> You define a table as: >>> "create table table_test (id as integer primary key, a as integer, b >>> as integer, c as integer)" >>> >>> Then you know that the "id" is unique and you only can have one row >>> with that integer. >>> >>> Then you can give the following statements: >>> insert or update into table_test (id,c) values (1,3) >>> >> >> REPLACE INTO table_test >>SELECT 1, a, b, 3 FROM table_test WHERE id=1 >>UNION ALL >>SELECT 1, NULL, NULL, 3 >>LIMIT 1; >> >> >>> insert or update into table_test (id,b) values (1,2) insert or update >>> into table_test (id,a) values (1,1) insert or update into table_test >>> (id,a) values (5,13) >>> >>> This result is the following set: >>> 1|1|2|3 >>> 5|13|| >>> >>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and >>> then issue an update statement. I think "insert or update" will >>> increase performance. >>> >>> If the "insert or update" can't perform its operation, it can issue a >>> SQLITE_AMBIGUOUS error. >>> >>> Best regards, >>> Patrik >>> >>> -- >>> ASCII ribbon campaign ( ) >>> against HTML e-mail X >>> www.asciiribbon.org / \ >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> > > -- > ASCII ribbon campaign ( ) > against HTML e-mail X > www.asciiribbon.org / \ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If you > have received it in error, you are on notice as to its status and accordingly > please notify us immediately by reply e-mail and then delete this message > from your system. Please do not copy it or use it for any purposes, or > disclose its contents to any person as to do so could be a breach of > confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
Use parameters and bind... REPLACE INTO table_test SELECT :key, a, b, :val FROM table_test WHERE id=:key UNION ALL SELECT :key, NULL, NULL, :val LIMIT 1; -Ursprüngliche Nachricht- Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] Gesendet: Donnerstag, 27. Juni 2013 15:01 An: General Discussion of SQLite Database Betreff: Re: [sqlite] request for feature: insert or update Thank you for your suggestion! I would like to have all in one statement, then "insert or update" is perfect. This is least error prone. If you split the statement into several, you will end up in more statements to test and more bugs. (You write "3" on two places.) Working with blobs, I expect this procedure to waste more time. > REPLACE INTO table_test >SELECT 1, a, b, 3 FROM table_test WHERE id=1 >UNION ALL >SELECT 1, NULL, NULL, 3 >LIMIT 1; /Patrik On 06/27/2013 02:46 PM, Richard Hipp wrote: > On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson > wrote: > >> Hi All! >> >> A feature I'm missing is a syntax like with "insert or update". >> >> You define a table as: >> "create table table_test (id as integer primary key, a as integer, b >> as integer, c as integer)" >> >> Then you know that the "id" is unique and you only can have one row >> with that integer. >> >> Then you can give the following statements: >> insert or update into table_test (id,c) values (1,3) >> > > REPLACE INTO table_test >SELECT 1, a, b, 3 FROM table_test WHERE id=1 >UNION ALL >SELECT 1, NULL, NULL, 3 >LIMIT 1; > > >> insert or update into table_test (id,b) values (1,2) insert or update >> into table_test (id,a) values (1,1) insert or update into table_test >> (id,a) values (5,13) >> >> This result is the following set: >> 1|1|2|3 >> 5|13|| >> >> Now I'm doing: "insert or ignore into table_test (id) values (1)" and >> then issue an update statement. I think "insert or update" will >> increase performance. >> >> If the "insert or update" can't perform its operation, it can issue a >> SQLITE_AMBIGUOUS error. >> >> Best regards, >> Patrik >> >> -- >> ASCII ribbon campaign ( ) >> against HTML e-mail X >> www.asciiribbon.org / \ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
Thank you for your suggestion! I would like to have all in one statement, then "insert or update" is perfect. This is least error prone. If you split the statement into several, you will end up in more statements to test and more bugs. (You write "3" on two places.) Working with blobs, I expect this procedure to waste more time. > REPLACE INTO table_test >SELECT 1, a, b, 3 FROM table_test WHERE id=1 >UNION ALL >SELECT 1, NULL, NULL, 3 >LIMIT 1; /Patrik On 06/27/2013 02:46 PM, Richard Hipp wrote: > On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson > wrote: > >> Hi All! >> >> A feature I'm missing is a syntax like with "insert or update". >> >> You define a table as: >> "create table table_test (id as integer primary key, a as integer, b as >> integer, c as integer)" >> >> Then you know that the "id" is unique and you only can have one row with >> that integer. >> >> Then you can give the following statements: >> insert or update into table_test (id,c) values (1,3) >> > > REPLACE INTO table_test >SELECT 1, a, b, 3 FROM table_test WHERE id=1 >UNION ALL >SELECT 1, NULL, NULL, 3 >LIMIT 1; > > >> insert or update into table_test (id,b) values (1,2) >> insert or update into table_test (id,a) values (1,1) >> insert or update into table_test (id,a) values (5,13) >> >> This result is the following set: >> 1|1|2|3 >> 5|13|| >> >> Now I'm doing: "insert or ignore into table_test (id) values (1)" and >> then issue an update statement. I think "insert or update" will increase >> performance. >> >> If the "insert or update" can't perform its operation, it can issue a >> SQLITE_AMBIGUOUS error. >> >> Best regards, >> Patrik >> >> -- >> ASCII ribbon campaign ( ) >> against HTML e-mail X >> www.asciiribbon.org / \ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson wrote: > Hi All! > > A feature I'm missing is a syntax like with "insert or update". > > You define a table as: > "create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > Then you know that the "id" is unique and you only can have one row with > that integer. > > Then you can give the following statements: > insert or update into table_test (id,c) values (1,3) > REPLACE INTO table_test SELECT 1, a, b, 3 FROM table_test WHERE id=1 UNION ALL SELECT 1, NULL, NULL, 3 LIMIT 1; > insert or update into table_test (id,b) values (1,2) > insert or update into table_test (id,a) values (1,1) > insert or update into table_test (id,a) values (5,13) > > This result is the following set: > 1|1|2|3 > 5|13|| > > Now I'm doing: "insert or ignore into table_test (id) values (1)" and > then issue an update statement. I think "insert or update" will increase > performance. > > If the "insert or update" can't perform its operation, it can issue a > SQLITE_AMBIGUOUS error. > > Best regards, > Patrik > > -- > ASCII ribbon campaign ( ) > against HTML e-mail X > www.asciiribbon.org / \ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
The SQLite syntax "replace into" does almost what I want. It also erases the other values of the same line, which I don't want. On 06/27/2013 01:38 PM, Simon Slavin wrote: > > On 27 Jun 2013, at 11:05am, Patrik Nilsson wrote: > >> A feature I'm missing is a syntax like with "insert or update". >> >> You define a table as: >> "create table table_test (id as integer primary key, a as integer, b as >> integer, c as integer)" >> >> Then you know that the "id" is unique and you only can have one row with >> that integer. >> >> Then you can give the following statements: >> insert or update into table_test (id,c) values (1,3) >> insert or update into table_test (id,b) values (1,2) >> insert or update into table_test (id,a) values (1,1) >> insert or update into table_test (id,a) values (5,13) >> >> This result is the following set: >> 1|1|2|3 >> 5|13|| > > I understand your desire for the combination 'INSERT OR UPDATE' but I think > there is too much danger of SQL not understanding whether the UPDATE should > be affecting a single row or multiple rows or one new row. The analysis > stage for the instruction would have to figure out whether you were correctly > specifying the whole of the primary key of the row. Given that there may be > no primary row specified in the table definition, or that the primary key may > be a compound key, and that you may be using different COLLATions, this > analysis would involve quite a few instructions to make sure it got > everything perfectly right, which means it would be slow. > > For further discussion on this search for the conventional term for what you > asked for: UPSERT, and for the endless discussion of exactly what the MERGE > does in discussion fora about other implementations of SQL. You will see > that even experienced SQL writers have a hard time explaining clearly what > MERGE does under different circumstances. > >> Now I'm doing: "insert or ignore into table_test (id) values (1)" and >> then issue an update statement. I think "insert or update" will increase >> performance. > > I think that’s the best way to clearly explain to SQLite what you want done. > I agree that it is two commands long, but it takes advantage of existing > SQLite statements and it’s impossible for someone reading your code to > misunderstand what you want to happen. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > . > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
On 06/27/2013 02:16 PM, Hick Gunter wrote:
>> -Ursprüngliche Nachricht- Von: Simon Slavin
>> [mailto:slav...@bigfraud.org] Betreff: Re: [sqlite] request for
>> feature: insert or update
>>
>>
>> On 27 Jun 2013, at 11:05am, Patrik Nilsson
>> wrote:
>>
>>
>>> Now I'm doing: "insert or ignore into table_test (id) values (1)"
>>> and then issue an update statement. I think "insert or update"
>>> will increase performance.
>>
>> I think that's the best way to clearly explain to SQLite what you
>> want done. I agree that it is two commands long, but it takes
>> advantage of existing SQLite statements and >it's impossible for
>> someone reading your code to misunderstand what you want to
>> happen.
>>
>> Simon.
>
> The sequence "insert or ignore" followed by "update" is IMHO
> indicative of file-oriented, procedural thinking, where you have to
> locate the record first and update the desired fields second (or fail
> to find a record and create a new one). Implementing this 1:1 in SQL
> (which is set-oriented and rule-based) causes the standard path of
> execution (record is already present) to always perform the exception
> case insert.
>
> That is why I proposed doing the standard path of execution (the
> update) first, and then checking for the exception condition (no rows
> processed) before doing the exception processing (insert with the
> given values).
>
It can be implemented in this way, but writing the same statement two
times means it is error prone. I want to write the statement once and
verify it.
Otherwise you can end up with bugs everywhere.
> While the first method will reliably run with implicit (autocommit)
> or explicit transactions, the second requires either an explicit
> transaction or a loop. Consider:
>
>
> BEGIN; UPDATE; if (no rows) then INSERT; COMMIT;
>
> --> the first thread to enter then transaction will do the INSERT -->
> all others do only the UPDATE
>
> repeat { UPDATE; If (no rows) then INSERT; } until (no error);
>
> --> If two threads happen to attempt the INSERT, one will fail -->
> The failing thread must retry the UPDATE
>
>
>
>
>
> --
>
>
Gunter Hick
> Software Engineer Scientific Games International GmbH Klitschgasse 2
> – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100
> 0 E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged.
> If you have received it in error, you are on notice as to its status
> and accordingly please notify us immediately by reply e-mail and then
> delete this message from your system. Please do not copy it or use it
> for any purposes, or disclose its contents to any person as to do so
> could be a breach of confidence. Thank you for your cooperation.
> ___ sqlite-users mailing
> list sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
ASCII ribbon campaign ( )
against HTML e-mail X
www.asciiribbon.org / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
It works without autoincrement too... asql> create temp table test (id integer primary key, a integer, b integer, c integer); asql> insert into test (a,b) values (1,2); rows inserted - 1 asql> select * from test; id a b c -- -- -- -- 1 1 2 NULL asql> insert into test (a,b) values (1,2); rows inserted - 1 asql> select * from test; id a b c -- -- -- -- 1 1 2 NULL 2 1 2 NULL -Ursprüngliche Nachricht- Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] Gesendet: Donnerstag, 27. Juni 2013 13:55 An: General Discussion of SQLite Database Betreff: Re: [sqlite] request for feature: insert or update You are right it won't fail if you define the table with autoincrement: "create table table_test (id as integer primary key AUTOINCREMENT, a as integer, b as integer, c as integer)" /Patrik On 06/27/2013 01:45 PM, Hick Gunter wrote: > Insert into table_test (a,b) values (1,2); inserts a new row with a default > id field (see SQLite documentation) and does not fail. > > Update table_test set a=1, b=2; modifies all rows of the table and does not > fail either. > > Why should "insert or update" fail??? > > -Ursprüngliche Nachricht- > Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] > Gesendet: Donnerstag, 27. Juni 2013 12:55 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] request for feature: insert or update > >> >> Which circumstances are you thinking of that would cause the proposed > "insert or update" to fail (other than those that would cause the update to > fail too)? > > It is a fail condition when the statement is ambiguous. > > create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > insert or update into table_test (id,c) values (1,3) (This is OK) > > insert or update into table_test (a,b) values (1,2) (It is not OK > since you don't have a unique value to relate to.) > > > On 06/27/2013 12:32 PM, Hick Gunter wrote: >> You can check the number of rows modified by an UPDATE statement >> using the sqlite3_changes() interface. (Using pragma count_changes is >> deprecated!) >> >> Within a transaction, when you issue an >> >> UPDATE table_test SET = WHERE id = ; >> >> and sqlite3_changes() returns 0 then you need to >> >> INSERT INTO table_test (id,) VALUES (,); >> >> otherwise you are good to go. >> >> Which circumstances are you thinking of that would cause the proposed >> "insert or update" to fail (other than those that would cause the update to >> fail too)? >> >> -Ursprüngliche Nachricht- >> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] >> Gesendet: Donnerstag, 27. Juni 2013 12:05 >> An: General Discussion of SQLite Database >> Betreff: [sqlite] request for feature: insert or update >> >> Hi All! >> >> A feature I'm missing is a syntax like with "insert or update". >> >> You define a table as: >> "create table table_test (id as integer primary key, a as integer, b as >> integer, c as integer)" >> >> Then you know that the "id" is unique and you only can have one row with >> that integer. >> >> Then you can give the following statements: >> insert or update into table_test (id,c) values (1,3) insert or update >> into table_test (id,b) values (1,2) insert or update into table_test >> (id,a) values (1,1) insert or update into table_test (id,a) values >> (5,13) >> >> This result is the following set: >> 1|1|2|3 >> 5|13|| >> >> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then >> issue an update statement. I think "insert or update" will increase >> performance. >> >> If the "insert or update" can't perform its operation, it can issue a >> SQLITE_AMBIGUOUS error. >> >> Best regards, >> Patrik >> >> -- >> ASCII ribbon campaign ( ) >> against HTML e-mail X >> www.asciiribbon.org / \ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> - >> - >> >> Gunter Hick >> Software Enginee
Re: [sqlite] request for feature: insert or update
>-Ursprüngliche Nachricht-
>Von: Simon Slavin [mailto:slav...@bigfraud.org]
>Betreff: Re: [sqlite] request for feature: insert or update
>
>
>On 27 Jun 2013, at 11:05am, Patrik Nilsson wrote:
>
>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will
>> increase performance.
>
>I think that's the best way to clearly explain to SQLite what you want done.
>I agree that it is two commands long, but it takes advantage of existing
>SQLite statements and >it's impossible for someone reading your code to
>misunderstand what you want to happen.
>
>Simon.
The sequence "insert or ignore" followed by "update" is IMHO indicative of
file-oriented, procedural thinking, where you have to locate the record first
and update the desired fields second (or fail to find a record and create a new
one). Implementing this 1:1 in SQL (which is set-oriented and rule-based)
causes the standard path of execution (record is already present) to always
perform the exception case insert.
That is why I proposed doing the standard path of execution (the update) first,
and then checking for the exception condition (no rows processed) before doing
the exception processing (insert with the given values).
While the first method will reliably run with implicit (autocommit) or explicit
transactions, the second requires either an explicit transaction or a loop.
Consider:
BEGIN;
UPDATE;
if (no rows) then INSERT;
COMMIT;
--> the first thread to enter then transaction will do the INSERT
--> all others do only the UPDATE
repeat {
UPDATE;
If (no rows) then INSERT;
} until (no error);
--> If two threads happen to attempt the INSERT, one will fail
--> The failing thread must retry the UPDATE
--
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
You are right it won't fail if you define the table with autoincrement: "create table table_test (id as integer primary key AUTOINCREMENT, a as integer, b as integer, c as integer)" /Patrik On 06/27/2013 01:45 PM, Hick Gunter wrote: > Insert into table_test (a,b) values (1,2); inserts a new row with a default > id field (see SQLite documentation) and does not fail. > > Update table_test set a=1, b=2; modifies all rows of the table and does not > fail either. > > Why should "insert or update" fail??? > > -Ursprüngliche Nachricht- > Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] > Gesendet: Donnerstag, 27. Juni 2013 12:55 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] request for feature: insert or update > >> >> Which circumstances are you thinking of that would cause the proposed > "insert or update" to fail (other than those that would cause the update to > fail too)? > > It is a fail condition when the statement is ambiguous. > > create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > insert or update into table_test (id,c) values (1,3) (This is OK) > > insert or update into table_test (a,b) values (1,2) (It is not OK since you > don't have a unique value to relate to.) > > > On 06/27/2013 12:32 PM, Hick Gunter wrote: >> You can check the number of rows modified by an UPDATE statement using >> the sqlite3_changes() interface. (Using pragma count_changes is >> deprecated!) >> >> Within a transaction, when you issue an >> >> UPDATE table_test SET = WHERE id = ; >> >> and sqlite3_changes() returns 0 then you need to >> >> INSERT INTO table_test (id,) VALUES (,); >> >> otherwise you are good to go. >> >> Which circumstances are you thinking of that would cause the proposed >> "insert or update" to fail (other than those that would cause the update to >> fail too)? >> >> -Ursprüngliche Nachricht- >> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] >> Gesendet: Donnerstag, 27. Juni 2013 12:05 >> An: General Discussion of SQLite Database >> Betreff: [sqlite] request for feature: insert or update >> >> Hi All! >> >> A feature I'm missing is a syntax like with "insert or update". >> >> You define a table as: >> "create table table_test (id as integer primary key, a as integer, b as >> integer, c as integer)" >> >> Then you know that the "id" is unique and you only can have one row with >> that integer. >> >> Then you can give the following statements: >> insert or update into table_test (id,c) values (1,3) insert or update >> into table_test (id,b) values (1,2) insert or update into table_test >> (id,a) values (1,1) insert or update into table_test (id,a) values >> (5,13) >> >> This result is the following set: >> 1|1|2|3 >> 5|13|| >> >> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then >> issue an update statement. I think "insert or update" will increase >> performance. >> >> If the "insert or update" can't perform its operation, it can issue a >> SQLITE_AMBIGUOUS error. >> >> Best regards, >> Patrik >> >> -- >> ASCII ribbon campaign ( ) >> against HTML e-mail X >> www.asciiribbon.org / \ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -- >> >> Gunter Hick >> Software Engineer >> Scientific Games International GmbH >> Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien >> Tel: +43 1 80100 0 >> E-Mail: h...@scigames.at >> >> This e-mail is confidential and may well also be legally privileged. If you >> have received it in error, you are on notice as to its status and >> accordingly please notify us immediately by reply e-mail and then delete >> this message from your system. Please do not copy it or use it for any >> purposes, or disclose its contents to any person as to do so could be a >> breach of confidence. Thank you for your cooperation. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> &g
Re: [sqlite] request for feature: insert or update
Insert into table_test (a,b) values (1,2); inserts a new row with a default id field (see SQLite documentation) and does not fail. Update table_test set a=1, b=2; modifies all rows of the table and does not fail either. Why should "insert or update" fail??? -Ursprüngliche Nachricht- Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] Gesendet: Donnerstag, 27. Juni 2013 12:55 An: General Discussion of SQLite Database Betreff: Re: [sqlite] request for feature: insert or update > > Which circumstances are you thinking of that would cause the proposed "insert or update" to fail (other than those that would cause the update to fail too)? It is a fail condition when the statement is ambiguous. create table table_test (id as integer primary key, a as integer, b as integer, c as integer)" insert or update into table_test (id,c) values (1,3) (This is OK) insert or update into table_test (a,b) values (1,2) (It is not OK since you don't have a unique value to relate to.) On 06/27/2013 12:32 PM, Hick Gunter wrote: > You can check the number of rows modified by an UPDATE statement using > the sqlite3_changes() interface. (Using pragma count_changes is > deprecated!) > > Within a transaction, when you issue an > > UPDATE table_test SET = WHERE id = ; > > and sqlite3_changes() returns 0 then you need to > > INSERT INTO table_test (id,) VALUES (,); > > otherwise you are good to go. > > Which circumstances are you thinking of that would cause the proposed "insert > or update" to fail (other than those that would cause the update to fail too)? > > -Ursprüngliche Nachricht- > Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] > Gesendet: Donnerstag, 27. Juni 2013 12:05 > An: General Discussion of SQLite Database > Betreff: [sqlite] request for feature: insert or update > > Hi All! > > A feature I'm missing is a syntax like with "insert or update". > > You define a table as: > "create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > Then you know that the "id" is unique and you only can have one row with that > integer. > > Then you can give the following statements: > insert or update into table_test (id,c) values (1,3) insert or update > into table_test (id,b) values (1,2) insert or update into table_test > (id,a) values (1,1) insert or update into table_test (id,a) values > (5,13) > > This result is the following set: > 1|1|2|3 > 5|13|| > > Now I'm doing: "insert or ignore into table_test (id) values (1)" and then > issue an update statement. I think "insert or update" will increase > performance. > > If the "insert or update" can't perform its operation, it can issue a > SQLITE_AMBIGUOUS error. > > Best regards, > Patrik > > -- > ASCII ribbon campaign ( ) > against HTML e-mail X > www.asciiribbon.org / \ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If you > have received it in error, you are on notice as to its status and accordingly > please notify us immediately by reply e-mail and then delete this message > from your system. Please do not copy it or use it for any purposes, or > disclose its contents to any person as to do so could be a breach of > confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
On 27 Jun 2013, at 11:05am, Patrik Nilsson wrote: > A feature I'm missing is a syntax like with "insert or update". > > You define a table as: > "create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > Then you know that the "id" is unique and you only can have one row with > that integer. > > Then you can give the following statements: > insert or update into table_test (id,c) values (1,3) > insert or update into table_test (id,b) values (1,2) > insert or update into table_test (id,a) values (1,1) > insert or update into table_test (id,a) values (5,13) > > This result is the following set: > 1|1|2|3 > 5|13|| I understand your desire for the combination 'INSERT OR UPDATE' but I think there is too much danger of SQL not understanding whether the UPDATE should be affecting a single row or multiple rows or one new row. The analysis stage for the instruction would have to figure out whether you were correctly specifying the whole of the primary key of the row. Given that there may be no primary row specified in the table definition, or that the primary key may be a compound key, and that you may be using different COLLATions, this analysis would involve quite a few instructions to make sure it got everything perfectly right, which means it would be slow. For further discussion on this search for the conventional term for what you asked for: UPSERT, and for the endless discussion of exactly what the MERGE does in discussion fora about other implementations of SQL. You will see that even experienced SQL writers have a hard time explaining clearly what MERGE does under different circumstances. > Now I'm doing: "insert or ignore into table_test (id) values (1)" and > then issue an update statement. I think "insert or update" will increase > performance. I think that’s the best way to clearly explain to SQLite what you want done. I agree that it is two commands long, but it takes advantage of existing SQLite statements and it’s impossible for someone reading your code to misunderstand what you want to happen. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
>I'll add a vote to this request. >It's not as if it is a hard thing to do, I continuously run update algorithms something like this: Correction - re-reading my statement it sounded very wrong, please allow me to rephrase: I'll add a vote to this request. It's not as if it is difficult for us to do right now - I achieve this in normal SQL simply by running update algorithms something like the following: ... etc. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
I'll add a vote to this request. It's not as if it is a hard thing to do, I continuously run update algorithms something like this: Query "SELECT Count() FROM t WHERE ID=XXX;" --> r; if (r>0) Execute "UPDATE t SET v=YYY WHERE ID=XXX;" else Execute "INSERT INTO t (XXX,YYY);" (I obviously use code a bit more efficient, but you get the idea). This works quite great, but I cant help think the SQLite engine would do a much much more efficient job of this internally in a single query with an SQL conflict clause as Patrik suggested (or something else with similar effect). It may also be that I am simply ignorant of an alread-existing way to do this efficiently, in which case please inform me - thanks! On 2013/06/27 12:05, Patrik Nilsson wrote: Hi All! A feature I'm missing is a syntax like with "insert or update". You define a table as: "create table table_test (id as integer primary key, a as integer, b as integer, c as integer)" Then you know that the "id" is unique and you only can have one row with that integer. Then you can give the following statements: insert or update into table_test (id,c) values (1,3) insert or update into table_test (id,b) values (1,2) insert or update into table_test (id,a) values (1,1) insert or update into table_test (id,a) values (5,13) This result is the following set: 1|1|2|3 5|13|| Now I'm doing: "insert or ignore into table_test (id) values (1)" and then issue an update statement. I think "insert or update" will increase performance. If the "insert or update" can't perform its operation, it can issue a SQLITE_AMBIGUOUS error. Best regards, Patrik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
> > Which circumstances are you thinking of that would cause the proposed "insert or update" to fail (other than those that would cause the update to fail too)? It is a fail condition when the statement is ambiguous. create table table_test (id as integer primary key, a as integer, b as integer, c as integer)" insert or update into table_test (id,c) values (1,3) (This is OK) insert or update into table_test (a,b) values (1,2) (It is not OK since you don't have a unique value to relate to.) On 06/27/2013 12:32 PM, Hick Gunter wrote: > You can check the number of rows modified by an UPDATE statement using the > sqlite3_changes() interface. (Using pragma count_changes is deprecated!) > > Within a transaction, when you issue an > > UPDATE table_test SET = WHERE id = ; > > and sqlite3_changes() returns 0 then you need to > > INSERT INTO table_test (id,) VALUES (,); > > otherwise you are good to go. > > Which circumstances are you thinking of that would cause the proposed "insert > or update" to fail (other than those that would cause the update to fail too)? > > -Ursprüngliche Nachricht- > Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] > Gesendet: Donnerstag, 27. Juni 2013 12:05 > An: General Discussion of SQLite Database > Betreff: [sqlite] request for feature: insert or update > > Hi All! > > A feature I'm missing is a syntax like with "insert or update". > > You define a table as: > "create table table_test (id as integer primary key, a as integer, b as > integer, c as integer)" > > Then you know that the "id" is unique and you only can have one row with that > integer. > > Then you can give the following statements: > insert or update into table_test (id,c) values (1,3) insert or update into > table_test (id,b) values (1,2) insert or update into table_test (id,a) values > (1,1) insert or update into table_test (id,a) values (5,13) > > This result is the following set: > 1|1|2|3 > 5|13|| > > Now I'm doing: "insert or ignore into table_test (id) values (1)" and then > issue an update statement. I think "insert or update" will increase > performance. > > If the "insert or update" can't perform its operation, it can issue a > SQLITE_AMBIGUOUS error. > > Best regards, > Patrik > > -- > ASCII ribbon campaign ( ) > against HTML e-mail X > www.asciiribbon.org / \ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If you > have received it in error, you are on notice as to its status and accordingly > please notify us immediately by reply e-mail and then delete this message > from your system. Please do not copy it or use it for any purposes, or > disclose its contents to any person as to do so could be a breach of > confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] request for feature: insert or update
You can check the number of rows modified by an UPDATE statement using the sqlite3_changes() interface. (Using pragma count_changes is deprecated!) Within a transaction, when you issue an UPDATE table_test SET = WHERE id = ; and sqlite3_changes() returns 0 then you need to INSERT INTO table_test (id,) VALUES (,); otherwise you are good to go. Which circumstances are you thinking of that would cause the proposed "insert or update" to fail (other than those that would cause the update to fail too)? -Ursprüngliche Nachricht- Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] Gesendet: Donnerstag, 27. Juni 2013 12:05 An: General Discussion of SQLite Database Betreff: [sqlite] request for feature: insert or update Hi All! A feature I'm missing is a syntax like with "insert or update". You define a table as: "create table table_test (id as integer primary key, a as integer, b as integer, c as integer)" Then you know that the "id" is unique and you only can have one row with that integer. Then you can give the following statements: insert or update into table_test (id,c) values (1,3) insert or update into table_test (id,b) values (1,2) insert or update into table_test (id,a) values (1,1) insert or update into table_test (id,a) values (5,13) This result is the following set: 1|1|2|3 5|13|| Now I'm doing: "insert or ignore into table_test (id) values (1)" and then issue an update statement. I think "insert or update" will increase performance. If the "insert or update" can't perform its operation, it can issue a SQLITE_AMBIGUOUS error. Best regards, Patrik -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
