updating a field without going through each record 2

[R17]

hello!

i use this command to update a logical field in table A which exists in table B.

SELE 1
USE a
GO TOP
SELE 2
USE b
GO TOP

SELE 1
DO WHILE .NOT. EOF()
SCATTER MEMVAR
SELE 2
LOCATE FOR m.checkno=checkno AND agycode=agycode
IF FOUND()
SELE 1
REPLACE ttype WITH .T.
ENDIF
SELE 1
SKIP
ENDDO


any better solution than this?

 

[R17]

please note that i issue another GO TOP before LOCATE

 

[spmall]

select 2
use b
index on str(checkno)+str(agycode) to temp
select 1
use a
set relation to str(checkno)+str(agycode) into b
replace all ttype with .t. for checkno=b.checkno and agycode=b.agycode
close all
erase temp.idx

 

[R17]

hi spmall,

unfortunately, the "set relation to str(checkno)+str(agycode) into b" returned a program error "alias not found. so i changed the into b to into 2.

however, the "replace all ttype with .t. for checkno=b.checkno and agycode=b.agycode" returned another program error "variable agycode not found"

any more help?

 

[JRB-Bldr]

There appears to be some confusion about which tables are open (in USE) or not.

Your original code:
SELE 1
USE a
GO TOP
SELE 2
USE b
GO TOP

SELE 1
DO WHILE .NOT. EOF()
SCATTER MEMVAR
SELE 2
LOCATE FOR m.checkno=checkno AND agycode=agycode
IF FOUND()
SELE 1
REPLACE ttype WITH .T.
ENDIF
SELE 1
SKIP
ENDDO

This code should create a situation where both A is used (in 1) and B is used (in 2) But in your 2nd post you say this is not the case since the program cannot find the alias "B".

First make certain that you have an Index created for B.

USE B IN 0 EXCLUSIVE
INDEX ON STR(checkno)+ STR(agycode) TAG key
USE

Now, why don't you simplify things:
USE A IN 0
USE B IN 0 ORDER key

SELECT A
SET RELATION TO STR(checkno)+ STR(agycode) INTO B
REPLACE ALL ttype WITH .T. ;
FOR (checkno = b.checkno AND agycode = b.agycode)
CLOSE DATABASES

Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[DSummZZZ]

GO TOP is unnecessary and is going to really slow things down. LOCATE FOR by default starts at beginning of file and looks at every record until it finds the first one matching the criteria.
SCATTER MEMVAR is also unnecessary since you're only replacing one field.
If you don't have indexes made, or if you want to be able to step through code and verify it is functioning correctly, you can do it without a SET RELATION:

SELE 1
USE a
SELE 2
USE b
*... GO TOP Not necessary. Default is BOF()

SELE a &&... I prefer aliases to numbers
SCAN
SELE b
LOCATE FOR b.checkno=a.checkno AND b.agycode=a.agycode
IF FOUND()
REPLACE a.ttype WITH .T.
ENDIF
ENDSCAN

It is still prefereable to use indexes though:

SELE 1
USE a
SELE 2
USE b EXCLUSIVE
*... do this if the fields are character data type
INDEX ON checkno + agycode

*... do this if the fields are numeric data type
INDEX ON STR(checkno) + STR(agycode) &&... numeric

*... GO TOP Not necessary. Default is BOF()

SELE a &&... I prefer aliases to numbers
SCAN
IF SEEK(STR(a.checkno) + STR(a.agycode), 'b')
REPLACE a.ttype WITH .T.
ENDIF
ENDSCAN

Dave S.

 

[R17]

im not sure but i think i didn't make myself clear..

i have table B say with 10 records and table A with 100 records. i have to check where all records from B exists in A, and if does i have to replace the "ttype" in A with .T.

im looking for a better and faster way to do this.

i've tried all your suggestions but it seemed that i got only one updated record in A.

is there any code where i dont have to step through each record in B? in my current table i have at least 5000 records in B, so it's sure a killer skipping in every record.

help pls?

 

[tesar]

R17,
you must be able repair minor errors in good respond..
*
in spmall missing ";":
...
replace all ttype with .t. for checkno=b.checkno .and. ; agycode=b.agycode
...
(without ";" second line load m.agycode - not exist)
*
in DSummZZZ, before replace missing select work area a.
*
If you want reduce searching records in B,
try create index in B as unique.
(for very big B it may be faster)
...
index on myexpres tag key uniq
copy to redb with cdx
use redb order tag key alias b
...
Tesar

 

[R17]

tesar,

yeah figured that out already.. im sorry i was just confused..

 

[DSummZZZ]

tesar,

in DSummZZZ, before replace missing select work area a.

It's not necessary to select the work area as long as you explicitly name the work area for the replace. As long as 'b' isn't at EOF(), hence the IF FOUND()..., it will replace the field in 'a'.
Dave S.

 

[tesar]

Dave, i regret a lot of futile rows
"select alias" in my programs.
Thank your respond i write help by "replace" -
you are right.
Star for you - it help in my programs...
Tesar

 

[R17]

i tried Dave's suggestion and in a matter of 30 secs table A is updated. great help!

 

[DSummZZZ]

Glad you got it working!
Thanks for the stars
Dave S.

 

[rgbean]

Gee Dave, now you're getting stars for FP being FAST!

Rick

 

[DSummZZZ]

Whatever it takes! ;-) Dave S.