Replacement issue

[Niki_S]

Table1

nStylecode     cLotName     cColorName     cStatus 
1234           A1           BLACK
1234           A1           WHITE
1234           A1           YELLOW
45             GP           WHITE
45             GP           BLACK

Table2

nStylecode     cLotName     cColorName     cLotColor      
1234           A1           2              2
45             GP           6              4

Now I want to update my cStatus field in Table1 with this condition.
If cColorName=cLotColor I want to update my cStatus with "Yes", if not I want to update cStatus as "No".

SELECT _Both
IF _Both.nStylecode=_DelDtl.nStylecode AND _Both.cLotName=_DelDtl.cLotName AND _Both.cColorName=_Both.cLotColor THEN 
	SELECT _DelDtl 
	replace nStatus WITH 'YES'
ELSE 
	SELECT _DelDtl
	replace nStatus WITH 'NO'

ENDIF

But with this I can't get any update. How can I do this?

Thank you

 

[GriffMG]

Is the replacement of nStatus a typo, should it be cStatus?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Niki_S]

Sorry it's my mistake.
I did it by using cStatus also. But it is not updating.

SELECT _Both
IF _Both.nStylecode=_DelDtl.nStylecode AND _Both.cLotName=_DelDtl.cLotName AND _Both.cColorName=_Both.cLotColor THEN 
	SELECT _DelDtl 
	replace cStatus WITH 'YES'
ELSE 
	SELECT _DelDtl
	replace cStatus WITH 'NO'

ENDIF

 

[GriffMG]

I don't think your structures will support your query, you have cColorName in both tables, one of which is a numeric the other a character field.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

Best guess though:

SELECT TABLE1
INDEX on STR(nStylecode,8,0)+cLotName+cColorName TAG Table1
SELECT TABLE2
INDEX on STR(nStylecode,8,0) TAG Table2
SELECT _DelDtl
GO Top
DO WHILE .not. EOF()
	SELECT TABLE1
	SET ORDER TO TABLE1
	SEEK STR(_DelDtl.nStyleCode,8,0)+_DelDtl.cLotName+_DelDetl.cColorName
	IF FOUND()	
		SELECT TABLE2
		SET ORDER TO TABLE2
		SEEK STR(TABLE1.nStyleCode,8,0)
		IF FOUND()
			SELECT _DelDtl 
			replace nStatus WITH 'YES'
		ELSE 
			SELECT _DelDtl
			replace nStatus WITH 'NO'
		ENDIF 
	ELSE
		SELECT _DelDtl
		replace nStatus WITH 'NO'
	ENDIF		
	SELECT _DelDtl
	SKIP
ENDDO

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​