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