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