This is a weird request. I have a table of data with no natural primary key. I need to update this table from a prior table, but some of the data fields can change over time, so I must be flexible on how I match.
So the matching I need to do is something like this, if Key1 is unique in both table, then match these records if Key1||Key2 is unique in both tables, then match these. if Key1||Key2||Key3 is unique in both tables, then match. if Key1||Key2||Key3||Key4 is unique in both tables, then match. The best I can think is to run 4 queries: UPDATE Table1 Set Value = (Select Value from Table2 where Key1 = Table1.Key1 and Key2 = Table1.Key2 and Key3 = Table1.Key3 and Key4 = Table1.Key4 Group By Key1, Key2, Key3, Key4 Having Count(*)=1); UPDATE Table1 Set Value = (Select Value from Table2 where Key1 = Table1.Key1 and Key2 = Table1.Key2 and Key3 = Table1.Key3 Group By Key1, Key2, Key3 Having Count(*)=1); UPDATE Table1 Set Value = (Select Value from Table2 where Key1 = Table1.Key1 and Key2 = Table1.Key2 Group By Key1, Key2 Having Count(*)=1); UPDATE Table1 Set Value = (Select Value from Table2 where Key1 = Table1.Key1 Group By Key1 Having Count(*)=1); It doesn't check that the match is unique on the Table1 side, but I think I can live with that. 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.... And if this is something better handled in the application code, can you show me some pseudo-code showing what that algorithm would look like? (Application will be written in Basic.) Thanks, David _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
