I'll add a vote to this request.
It's not as if it is a hard thing to do, I continuously run update algorithms 
something like this:

Query "SELECT Count() FROM t WHERE ID=XXX;" --> r;
if (r>0) Execute "UPDATE t SET v=YYY WHERE ID=XXX;" else Execute "INSERT INTO t 
(XXX,YYY);"

(I obviously use code a  bit more efficient, but you get the idea).

This works quite great, but I cant help think the SQLite engine would do a much much more efficient job of this internally in a single query with an SQL conflict clause as Patrik suggested (or something else with similar effect). It may also be that I am simply ignorant of an alread-existing way to do this efficiently, in which case please inform me - thanks!


On 2013/06/27 12:05, Patrik Nilsson 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)
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


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to