updating multiple records

[prakashroch]

i have two tables one is try3 and other try4

Field try3 ->Itemname,Sales,Itemid
in try3 Itemname and itemid colums are are alredy filled and sales for each record is "Zero" ,there are 100 such records

Field try4 ->Sales,Itemid
Each distinct itemid has sales value

i want to assign itemid from table try4 to records in try3

can it be done in single SQl statemnt or do i have to use loop to read each itemid???

Thanks

 

[MCTCO]

TRY THIS

SELECT TRY4
GO TOP
SCAN FOR (YOUR CRITERIA)
&&SET YOUR VARIABLES HERE
REPLACE TRY3.FIELD(S) WITH (YOUR VARIABLES)FOR TRY3.FIELD=(YOUR CRITERIA)
ENDSCAN

FOXPRO WILL SCAN THE TABLE AND EXECUTE THE CODE FOR EACH RECORD THAT MEETS YOUR SEARCH CRITERIA.

 

[Guest_imported]

First of all, I should not use column Sales in table try3, it is redundant. The only reason to do this from my point of view is performance.

But OK, if you want column Sales, this would be the SQL statement:

update try3 t3
set t3.sales = select t4.sales from try4 t4 where t3.itemid=t4.itemid

Note: itemid should be unique!