Thanks Simon -- this is just a minimal example. The WHERE clause is needed
because, unless I would provide a CASE WHEN for every single (key, value),
then the UPDATE would set the "extra" value of any unmatched key, value to
NULL.
Please remember this is just an example. I'd like to generalize this kind
of approach. It works fine for scalar primary keys, but is having issues
when the key is composed of multiple columns.
On Tue, Apr 23, 2019 at 9:35 AM Simon Slavin <slav...@bigfraud.org> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
