Okay, this trigger what works. Not really an answer, since there's no
simplification achieved. Still thought I'd post it though, as a
conclusion-of-sorts nonetheless. It's neither clever nor elegant, but it
does work. Hammer and tongs, is what it is. There's no decision tree for
choosing when to update correctly, only updates that fail at the end for
not having matched when the very last "=" comes up "nope".
Now having hammered past that (because I'm stubborn) now I feel free to
attempt other ways, firstly a query to aggregate the same kind of data
instead as a view. Already I have puzzled out a way involving several
queries, one of which builds a temporary table. But it seems theres no
way to make that a single, all-in-one-go kind of query. At least not as
yet. Good thing there's Perl. I know that I can cue (queue?) them with
Perl. But just at the moment, that's not the point. I'm wanting to teach
myself pure-SQL ways, where such exist. Thanks to all who offered advice.
-- Accumulate for 160m band, QSO-by-QSO.
DROP TRIGGER IF EXISTS _160m_USA_Increment;
CREATE TRIGGER _160m_USA_Increment
AFTER UPDATE ON qso WHEN NEW.freq LIKE '1.%'
BEGIN
UPDATE world
SET _160m = (SELECT _160m+1 FROM world WHERE country = (SELECT
country FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM world WHERE country = (SELECT
country FROM qth WHERE rowid = NEW.qth_other_id))
WHERE country = (SELECT country FROM qth WHERE rowid =
NEW.qth_other_id);
UPDATE usa_state
SET _160m = (SELECT _160m+1 FROM usa_state WHERE state =
(SELECT state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM usa_state WHERE state =
(SELECT state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE state = (SELECT state FROM qth WHERE rowid =
NEW.qth_other_id)
AND 'USA' = (SELECT country FROM qth WHERE rowid =
NEW.qth_other_id);
UPDATE mx_state
SET _160m = (SELECT _160m+1 FROM mx_state WHERE state = (SELECT
state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM mx_state WHERE state = (SELECT
state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE state = (SELECT state FROM qth WHERE rowid =
NEW.qth_other_id)
AND 'Mexico' = (SELECT country FROM qth WHERE rowid =
NEW.qth_other_id);
UPDATE ca_prov
SET _160m = (SELECT _160m+1 FROM ca_prov WHERE prov = (SELECT
state FROM qth WHERE rowid = NEW.qth_other_id)),
count = (SELECT count+1 FROM ca_prov WHERE prov = (SELECT
state FROM qth WHERE rowid = NEW.qth_other_id))
WHERE prov = (SELECT state FROM qth WHERE rowid =
NEW.qth_other_id)
AND 'Canada' = (SELECT country FROM qth WHERE rowid =
NEW.qth_other_id);
END;
--
Mistera Sturno - Rarest Extinct Bird
<(+)__ Ĝan Ŭesli Starling
((__/)=- Holland, MI, USA
`||`
++ http://starling.us
http://esperanto.us
http://ky8d.net
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
