On 23 Apr 2019, at 3:11pm, Charles Leifer <[email protected]> wrote:
> UPDATE "bu" SET "extra" = CASE ("key", "value")
> WHEN ('k1', 1) THEN 100
> WHEN ('k2', 2) THEN -200
> WHEN ('k3', 3) THEN 30
> END
> WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));
Your WHERE clause it not needed, and SQLite is not clever enough to realise it
can be used to find specific rows in the table. (At least that's what I think,
I may be wrong.).
I'm not certain that SQLite understands your use of the bracketed terms. This
UPDATE "bu" SET "extra" = CASE "key"||"value"
WHEN 'k1'||1 THEN 100
WHEN 'k2'||2 THEN -200
WHEN 'k3'||3 THEN 30
END;
might work because || is the 'append' operator. Of course this assumes that
there are no ambiguities like 'k451' which might be key 'k4' or k45. It might
be better to use something like "key"|'x'|"value" .
But in fact this would be far faster
BEGIN;
UPDATE "bu" SET "extra" = 100 WHERE "key"='k1' AND "value"=1;
UPDATE "bu" SET "extra" = -200 WHERE "key"='k2' AND "value"=2;
UPDATE "bu" SET "extra" = 30 WHERE "key"='k3' AND "value"=3;
END;
because your WHERE clause matches a UNIQUE key, so SQLite could go straight to
the correct row rather than iterating through the entire table. So instead of
one command that has to read every row of the table you have three commands
which each execute almost instantly.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
