On 23 Apr 2019, at 3:11pm, Charles Leifer <colei...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users