Don't you simply want this? sqlite> create table real (id integer primary key, value integer); sqlite> create table shadow (id integer primary key, value integer); sqlite> insert into real (id, value) VALUES (1, 1); sqlite> insert into real (id, value) VALUES (2, 2); sqlite> insert into real (id, value) VALUES (3, 3); sqlite> insert into real (id, value) VALUES (4, 4); sqlite> insert into shadow (id, value) VALUES (2, 22);
sqlite> select ifnull(nullif(s.value, r.value), r.value) as value FROM real r LEFT OUTER JOIN shadow s ON s.id=r.id; 1 22 3 4 Or am I misunderstanding? Regards, Jonas On Jul 20, 2011, at 5:27 PM, Simon Slavin wrote: > > On 20 Jul 2011, at 4:21pm, KeithB wrote: > >> 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. >> >> I can do this with two SELECTs, but that doesn't seem to be the most >> efficient approach. I'm sure this is easy and obvious with some >> combination of UNION, ORDER BY, and LIMIT or something, but I can't >> figure it out. > > Had you considered a VIEW ? A VIEW is a way of saving a SELECT statement > that can be accessed as if it's a TABLE. You could use a SELECT that uses a > JOIN to pick a value from the appropriate TABLE. > > Simon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
