On Sat, 7 Jan 2017 09:27:57 -0500, Gan Uesli Starling <g...@starling.us> wrote:
> 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? SQLite doesn't support variables in the way you try to use here. You can try one of the following: 1- Replace every c and s in the UPDATE statements by (SELECT ...) 2- prefix every UPDATE with a common tabel expression that delivers the reuired values, and use them with a (SELECT ...) as above 3- perhaps do something smart with an updateble view using an INSTEAD OF trigger, and supply the values once to the UPDATE myview statement. > -- 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; -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
