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
