On Mar 11, 2013, at 4:54 PM, David Bicking <[email protected]> wrote:
> Am I missing an obviously better way to do it?
> A way that can easily be expanded when they come back to me and say if I
> looked at a fifth column, you'd have been able to match it….
As they stand, your updates will always match whatever was computed last as you
don't have a where clause in your update statements. Most likely not what you
want.
If only SQLite had a merge statement, this would all be much easier.
Anyhow, personally, I would decompose the problem into two steps:
(1) How to join Table1 to Table2 considering these various keys.
(2) How to update Table2 with Table2's value given (1)
For (1), you have two main options: (a) keys concatenation or (b) a series of
left joins
(warning: pseudo code ahead)
(a)
select Table1.row_id as t1_row_id,
Table2.value
from (
select rowid as row_id,
key1 || '.' || key2 || '.' || key3 || '.' || key4 || '.' ||
key5 as key
from Table1
)
as Table1
left join (
select rowid as row_id,
key1 || '.' || key2 || '.' || key3 || '.' || key4 || '.' ||
key5 as key,
value
from Table2
)
as Table2
on Table2.key = Table1.key
The above will always result in two full table scan.
(b)
select Table1.rowid as t1_row_id,
coalesce( Level5.value, Level4.value, Level3.value, Level2.value,
Level1.value ) as value
from Table1
left join Table2
as Level5
on Level5.key1 = Table1.key1
and Level5.key2 = Table1.key2
and Level5.key3 = Table1.key3
and Level5.key4 = Table1.key4
and Level5.key5 = Table1.key5
left join Table2
as Level4
on Level4.key1 = Table1.key1
and Level4.key2 = Table1.key2
and Level4.key3 = Table1.key3
and Level4.key4 = Table1.key4
and Level5.key1 is null
left join Table2
as Level3
on Level3.key1 = Table1.key1
and Level3.key2 = Table1.key2
and Level3.key3 = Table1.key3
and Level4.key1 is null
left join Table2
as Level4
on Level4.key1 = Table1.key1
and Level4.key2 = Table1.key2
and Level3.key1 is null
left join Table2
as Level5
on Level5.key1 = Table1.key1
and Level4.key1 is null
While the second option looks more verbose, it may be more appropriate if
Table2 is small in relation to Table1, and Table1 can be pruned by key1 at the
very least.
(2) Once you have the data joined, the update itself is much more
straightforward. Wrap one of the select as a 'create temporary table t2t as'
and use that in the update statement:
update Table1
set value = ( select value from t2t where t2t.row_id = Table1.rowid )
where exists
(
select 1
from t2t
where t2t.row_id = Table1.rowid
)
As always, YMMV.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
