Hi all, I can't work out how to do something I feel ought to be easy. Attached small file is designed to be .read to provide a test case. Explanation of what I'm trying to follows below.
I have two separate but very similar source data sets, one for mobile one for fixed calls of various sub-types. My target table has a column in (defined as cost REAL) which I want to update based on the values in the sources. Target has a field in which allows me to distinguish mobile rows from fixed call rows, plus a field which has the call sub-types in. The approach I tried was to perform 2 sequential updates using first the fixed call source table then the mobile. I tried to restrict the update to rows in target where the class of call (fixed or mobile) corresponded to the content of the source. What seems to happen is that the second update blats the updates performed by the first: I can have either fixed results or mobile results but not both. I'm pretty sure I'm doing something wrong but haven't been able to find examples which show me how to resolve. Any pointers much appreciated... Regards, Amit Version 3.7.3 on Windows XP [For the time being I have split the target into two and done the individual updates and reassembled. Works - but can't quite believe it's the most elegant solution]
/* Update one table with values from another sources 1 & 2 represent fixed and mobile datasets. table target contains mixed data */ drop table if exists source1; create table source1 ( Aend TEXT, type TEXT, cost REAL ); INSERT INTO source1 VALUES('ALF','TFD',1.0); INSERT INTO source1 VALUES('ALF','DTF',2.1); INSERT INTO source1 VALUES('ALF','CP',0.5); INSERT INTO source1 VALUES('ALF','ITF',0.4); INSERT INTO source1 VALUES('ALF','UIFN',0.760); INSERT INTO source1 VALUES('ALF','SC',-1.0); drop table if exists source2; create table source2 ( Aend TEXT, type TEXT, cost REAL ); INSERT INTO source2 VALUES('ALF','TFD',21.0); INSERT INTO source2 VALUES('ALF','DTF',22.1); INSERT INTO source2 VALUES('ALF','CP',20.5); INSERT INTO source2 VALUES('ALF','ITF',20.4); INSERT INTO source2 VALUES('ALF','UIFN',20.760); drop table if exists target; create table target ( Aend TEXT, type TEXT, FM TEXT, cost REAL ); INSERT INTO target VALUES('ALF','TFD','Mobile',0.0); INSERT INTO target VALUES('ALF','UIFN','Mobile',0.0); INSERT INTO target VALUES('ALF','DFT','Mobile',0.0); INSERT INTO target VALUES('ALF','CP','Mobile',0.0); INSERT INTO target VALUES('ALF','SC','Mobile',0.0); INSERT INTO target VALUES('ALF','UIFN','Fixed',0.0); INSERT INTO target VALUES('ALF','CP','Fixed',0.0); INSERT INTO target VALUES('ALF','SC','Fixed',0.0); INSERT INTO target VALUES('ALF','TFD','Fixed',0.0); /* Syntax to update one table with values from another */ update target set cost = (select cost from source1 where source1.Aend=target.Aend and source1.type=target.type and FM='Fixed'); select * from target; select "Split"; update target set cost = (select cost from source2 where source2.Aend=target.Aend and source2.type=target.type and FM='Mobile'); /* Highlight that the data in the cost column after the first update has been over-written by the second which was not what was required. Implies that the only way to make the update process work (as opposed to using other joining techniques) is to partition the data set into separate fixed / mobile tables, perform separate updates and then recombine the updated data. */ select * from target;
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users