On Wed, Jul 20, 2011 at 04:52:11PM +0000, [email protected] scratched on
the wall:
> 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.
Good point. You could do the same basic thing, using over.id as a
check. Since the id column (in my example) is an INT-PK, it cannot be
NULL except due to an unmatched OUTER JOIN. This makes it a good
test value.
You still have to deal with each column individually, but this should
do the trick:
SELECT CASE WHEN over.id IS NULL THEN real.a ELSE over.a END AS a,
CASE WHEN over.id IS NULL THEN real.b ELSE over.b END AS b,
CASE WHEN over.id IS NULL THEN real.c ELSE over.c END AS c
FROM real LEFT OUTER JOIN over USING ( id );
> 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?
If the ID is a PK or otherwise indexed, both approaches are likely to
be quite fast. The disadvantage of the UNION approach is that it
depends on the fact you only want to return one line. Using the
OUTER JOIN allows you to use arbitrary WHERE clauses, including
queries that return multiple rows. That said, most people would
likely consider the UNION approach easier to understand.
-j
>
> 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.
--
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
