Re: [sqlite] Selective update of a column
Simon - thanks v. much. Makes complete sense now - and it does precisely
what I wanted. :)
{Appreciate the tip on the index - performance hasn't been an issue yet but
I'm sure I'll end up using it.}
On Wed, Nov 17, 2010 at 12:51 PM, Simon Slavin wrote:
>
> On 16 Nov 2010, at 3:29pm, Amit Chaudhuri wrote:
>
> > 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.
>
> You /nearly/ got it right. Your commands were
>
>update target set cost = (select cost from source1 where
> source1.Aend=target.Aend and source1.type=target.type and FM='Fixed');
>
>update target set cost = (select cost from source2 where
> source2.Aend=target.Aend and source2.type=target.type and FM='Mobile');
>
> Your problem is that you have the clause about FM in the wrong part. The
> FM column is in the table you're updating, not in the source. So the clause
> about FM should be part of the UPDATE command, not part of the SELECT. Your
> code should look more like
>
>UPDATE target SET cost = (SELECT cost FROM source1 WHERE
> source1.Aend=target.Aend AND source1.type=target.type) WHERE FM='Fixed';
>
>UPDATE target SET cost = (SELECT cost FROM source2 WHERE
> source2.Aend=target.Aend AND source2.type=target.type) WHERE FM='Mobile';
>
> I haven't tried the above code but I hope it might point you in the right
> direction.
>
> To make things fast, don't forget to index your source* tables on
> (Aend,type) or something like that.
>
> Simon.
> ___
> 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
Re: [sqlite] Selective update of a column
On 16 Nov 2010, at 3:29pm, Amit Chaudhuri wrote: > 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. You /nearly/ got it right. Your commands were update target set cost = (select cost from source1 where source1.Aend=target.Aend and source1.type=target.type and FM='Fixed'); update target set cost = (select cost from source2 where source2.Aend=target.Aend and source2.type=target.type and FM='Mobile'); Your problem is that you have the clause about FM in the wrong part. The FM column is in the table you're updating, not in the source. So the clause about FM should be part of the UPDATE command, not part of the SELECT. Your code should look more like UPDATE target SET cost = (SELECT cost FROM source1 WHERE source1.Aend=target.Aend AND source1.type=target.type) WHERE FM='Fixed'; UPDATE target SET cost = (SELECT cost FROM source2 WHERE source2.Aend=target.Aend AND source2.type=target.type) WHERE FM='Mobile'; I haven't tried the above code but I hope it might point you in the right direction. To make things fast, don't forget to index your source* tables on (Aend,type) or something like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selective update of a column
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
