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
