Let's say I have a simple table with a composite primary key (key, value)
and an extra field:
CREATE TABLE IF NOT EXISTS "bu" (
"key" TEXT NOT NULL,
"value" INTEGER NOT NULL,
"extra" INTEGER NOT NULL,
PRIMARY KEY ("key", "value"));
I'll put 3 rows in the table:
INSERT INTO "bu" ("key", "value", "extra")
VALUES ('k1', 1, 1), ('k2', 2, 2), ('k3', 3, 3);
Now -- I want to do a "bulk update":
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));
I run into a "row value misused" error triggered by the WHERE clause, which
is comparing the (key, value) tuple to a set of row-values.
I've found that I can get this to work by using explicitly using VALUES:
UPDATE "bu" SET "extra" = CASE ("key", "value") ... END
WHERE ("key", "value") IN (VALUES ('k1', 1), ('k2', 2), ('k3', 3));
My question, though, is why is the VALUES bit needed for the WHERE clause
in this case? Reading the docs on row values, it seemed to me that
comparing two row values should work fine:
Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.
Is there a reason I need to specify VALUES instead of simply passing in a
set of row tuples? It seems a little bit inconsistent to me. Thanks!
Charlie
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
