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
