So I'm trying to accumulate data for state/prov inside of USA, Canada
and Mexico, and country for the rest of the world.
Since country and state from the same update are factors in incrementing
each of four tables, I was hoping to simplify it with a single select
with 'AS' and then re-use the AS-value in every case. This as opposed to
the redundancy of selecting the same thing anew every time. See as
below. It passes syntax but fails on exectute, saying there is no column
's'. Is there some way to do this? To hold the 'state' and 'country' and
reuse them like a variable from one query to the next for sequential
comparisons?
-- Trigger for incrementing country & region counts for 160m band,
QSO-by-QSO.
DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
NEW.freq LIKE '1.%'
BEGIN
SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
SELECT state AS s FROM qth WHERE rowid = NEW.qth_other_id;
UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE
state = s) WHERE state = s AND c = 'USA';
UPDATE mx_state SET _160m = 1 + (SELECT _160m FROM mx_state WHERE
state = s) WHERE state = s AND c = 'Mexico';
UPDATE ca_prov SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE
prov = s) WHERE prov = s AND c = 'Canada';
UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE
country = c) WHERE country = c;
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
