On Sunday, 5 January, 2020 16:39, gideo...@lutzvillevineyards.com wrote:
>I have the following SQLITE query :
>
>SELECT BlokkeklaarAliasnaam,
BlokkeklaarKultivar,
sum(BlokkeklaarSkatting)
>FROM Blokkeklaar
>GROUP BY BlokkeklaarAliasnaam,
BlokkeklaarKultivar;
>
>I cannot figure out how to update a column (i.e.
>BlokkeklaarSkattingKultAliasnaam) with the value of
>sum(BlokkeklaarSkatting)
>in the above statement.
>This refers to a single table.
First of all, you cannot perform updates with a SELECT statement -- you need an
UPDATE statement for that ;)
UPDATE Blokkeklaar as O
SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS
O.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS
O.BlokkeklaarKultivar);
You may use triggers so that Blokkeklaar.BlokkeklaarSkattingKultAliasnaam is
always kept up-to-date (assuming that you start from an empty table or run the
above update once to make it current before making further updates, after which
you never need to run the above update again):
CREATE TRIGGER ins_Blokkeklaar AFTER INSERT ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS
O.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS
O.BlokkeklaarKultivar)
WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;
CREATE TRIGGER del_Blokkeklaar AFTER DELETE ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS
O.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS
O.BlokkeklaarKultivar)
WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar;
END;
CREATE TRIGGER upd_Blokkeklaar AFTER UPDATE OF BlokkeklaarSkatting,
BlokkeklaarAliasnaam, BlokkeklaarKultivar ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS
O.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS
O.BlokkeklaarKultivar)
WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar
AND (OLD.BlokkeklaarAliasnaam IS NOT NEW.BlokkeklaarAliasnaam
OR OLD.BlokkeklaarKultivar IS NOT NEW.BlokkeklaarKultivar);
UPDATE Blokkeklaar AS O
SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS
O.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS
O.BlokkeklaarKultivar)
WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;
You will, of course, need an index on Blokkeklaar (BlokkeklaarAliasnaam,
BlokkeklaarKultivar) unless you cherish slowness due to table scans -- it will
be even faster if this is a covering index such as Blokkeklaar
(BlokkeklaarAliasnaam, BlokkeklaarKultivar, BlokkeklaarSkatting).
And you may, of course, use == and <> in place of IS and IS NOT if the
correponding columns are constrained NOT NULL in the table definition.
Otherwise, the above treats NULL as a distinct value. If you want NULL to be
indistinct you will need to make some changes.
Also, note that the sum(...) aggregate returns NULL if there is nothing to sum
or all the values to sum are NULL. If you want that to be 0.0 instead, then
change all uses of sum(...) to total(...)
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
