The SQLite syntax "replace into" does almost what I want. It also erases the other values of the same line, which I don't want.
On 06/27/2013 01:38 PM, Simon Slavin wrote: > > On 27 Jun 2013, at 11:05am, Patrik Nilsson <nipatriknils...@gmail.com> wrote: > >> 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|| > > I understand your desire for the combination 'INSERT OR UPDATE' but I think > there is too much danger of SQL not understanding whether the UPDATE should > be affecting a single row or multiple rows or one new row. The analysis > stage for the instruction would have to figure out whether you were correctly > specifying the whole of the primary key of the row. Given that there may be > no primary row specified in the table definition, or that the primary key may > be a compound key, and that you may be using different COLLATions, this > analysis would involve quite a few instructions to make sure it got > everything perfectly right, which means it would be slow. > > For further discussion on this search for the conventional term for what you > asked for: UPSERT, and for the endless discussion of exactly what the MERGE > does in discussion fora about other implementations of SQL. You will see > that even experienced SQL writers have a hard time explaining clearly what > MERGE does under different circumstances. > >> 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. > _______________________________________________ > 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
