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 <slav...@bigfraud.org> 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
