I probably didn't explain it well enough, but my goal is that if a row exists in the override table then ALL if the values from that table are used. It doesn't seem like Jay's approach will accomplish that since it will only override non-NULL columns.
I did get it working (I think) by using: select a,b,c,1 as tablename from real where id=123 union select a,b,c,2 as tablename from over where id=123 order by tablename desc limit 1 But I have no idea whether that is the most efficient way to do this or not. How would I do this with a VIEW? Keith On Jul 20, 2011 11:35am, "Jay A. Kreibich" <[email protected]> wrote: > 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. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
