Ouch, I certainly did forget the where clauses! Table1 and Table2 are going to be roughly the same size. What I am trying to do, btw, is to take an extract from a prior period, which has had the "Value" added to it, and move that value in to the matching record in the new extract. There is a natural key in the table that is being extracted, but the columns that make up that natural key are not exposed to us mere mortals. Of the fields I have to play with, only key1 is actually part of the natural key in the source table. The others are fields which could be freely changed from one month to another. The higher the key number, the most likely it is to be changed from one month to another.
In your selects below, lets say we have two records in table2 which have exactly the same keys as a record in Table1, won't the select then spit out two values in the select? Or am I missing how that case is excluded? And should I take it the two last Left joins should have been Level2 and Level1, and not repeat the use of Level4 and Level5? And your option (a) is only one of 5 selects, each having one less key? For (b), lets say: Table1 Rowid|Key1 | Key2 | Key3 | Key4 | Key5 1 1 2 3 4 5 Table2 Rowid|Key1 | Key2 | Key3 | Key4 | Key5|Value 11 1 2 3 4 5 x 12 1 2 3 4 -5 y Just looking at levels 4 and 5 wouldn't the results be... Table1.Rowid | Level5.Rowid | Level4.Rowid | Value 1 11 12 x (from level 5)1 null 11 y (from level 4) 1 null 12 y (from level 4) Or am I mis-interpreting what you wrote? Um, I am wrong, cause I just tried it and sqlite only returns the level 5 result. I have no clue why! David ________________________________ From: Petite Abeille <petite.abei...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Monday, March 11, 2013 3:24 PM Subject: Re: [sqlite] Fuzzy joins 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
