On Mar 11, 2013, at 4:54 PM, David Bicking <dbic...@yahoo.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users