>-----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 <nipatriknils...@gmail.com> 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
