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