update-sql 2

[sqlpro]

hi friends

i am trying update a table with the values from a cursor
where it matches a condition.the code is

update master set town=curtemp.town 
where master.streetname in (select name from curtemp)
and empty(town)

but its updating with wrong values! am i doing any wrong?
Thanks for ur ideas

cheers

 

[jimstarr]

Try:

and empty(master.town)

Jim

 

[sqlpro]

i did but to no avail.
if it was sql server i would try like

update master set master.town=curtemp.town 
from master,curtemp 
where master.streetname=curtemp.name and empty(master.town)

it works perfectly but in vfp it does not allow FROM clause
in UPDATE statements.
anyway i got around writing SCAN..ENDSCAN on curtemp.

cheers

 

[wgcs]

The fact with VFP is that you can't rely on the position of the record pointer in a sub-query in order to refer to fields in that sub-query from the main query. So, while the filtering works fine on the UPDATE, there is no guarantee (and no implication) as to which record curtemp.town will get the "town" from.

 

[sqlpro]

hi wgcs
so r u saying that we cannot write a single update statement
in vfp to update a table with the values from another.
Thanks

cheers

 

[wgcs]

Right.

However, you can set a relationship, then REPLACE ALL:

USE MyCustomers
SELECT 0
USE MyFriends
INDEX ON PersonID TAG PersonID

SELECT MyCustomers
SET RELATION TO PersonID INTO MyFriends

REPLACE ALL MyCustomers.NickName WITH MyFriends.NickName ;
  FOR NOT EMPTY(MyFriends.Nickname) ;
  AND NOT EMPTY(MyCustomers.NickName) ;
  AND MyCustomers.Nick_Age<MyFriends.Nick_Age

etc..

(of course, the index would already exist, and you'd use SET ORDER TO instead.... the above is just for illustration)

 

[sqlpro]

Thanks wgcs
ur example very useful

cheers