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 <field>=<value> WHERE id = <key>; >> >> and sqlite3_changes() returns 0 then you need to >> >> INSERT INTO table_test (id,<field>) VALUES (<key>,<value>); >> >> 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 > -- 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
