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
