On Wed, Jul 20, 2011 at 11:21:32AM -0400, KeithB scratched on the wall: > I'd like to create a temporary table to "shadow" one of my persistent > tables. It will have the same columns and hold "override" values that, > when present, take precedence over the real values. So, I'd like to > search for a row by primary key, looking first in the override table, > and if not found, then in the real table. The result will always be > zero or 1 rows.
This is a bit ugly, but... sqlite> sqlite> CREATE TABLE main.real ( id INTEGER PRIMARY KEY, a, b, c ); sqlite> CREATE TEMP TABLE over ( id INGEGER PRIMARY KEY, a, b, c ); sqlite> sqlite> INSERT INTO real VALUES ( 1, 1, 10, 100 ); sqlite> INSERT INTO real VALUES ( 2, 2, 20, 200 ); sqlite> INSERT INTO real VALUES ( 3, 3, 30, 300 ); sqlite> sqlite> INSERT INTO over VALUES ( 2, 2, 22, 222 ); sqlite> sqlite> SELECT coalesce( over.a, real.a ) AS a, ...> coalesce( over.b, real.b ) AS b, ...> coalesce( over.c, real.c ) AS c ...> FROM real LEFT OUTER JOIN over USING ( id ); 1|10|100 2|22|222 3|30|300 sqlite> The order of the join and coalesce() parameters is important. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
