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
