How to update maximum number in foxpro table

[Niki_S]

table1
nStylecode     cLotName     Delvr_Pcs 
33600          A            150

I have a table like this and now I want to update my another table using table1 and I want to update the maximum record of table2 like this.

nStylecode     cLotName     Delvr_Pcs     Color
33600          A            170           BLACK
33600          A            100           RED
33600          A            50            BLUE

The final result that I want is like this.

nStylecode     cLotName     Delvr_Pcs     Color
33600          A            [b]150[/b]           BLACK
33600          A            100           RED
33600          A            50            BLUE

How can I do this?

Thank you

 

[Chris Miller]

Have an index on Delvr_Pcs or query with ORDER BY Delvr_Pcs DESCENDING.

And then just replace in the first record.

Chriss

 

[Niki_S]

If I have something like this,

nStylecode     cLotName     Delvr_Pcs   
33600          A            100           
33600          A            100          
33600          A            100

and I want my output ike this,

nStylecode     cLotName     Delvr_Pcs     
33600          A            150           
33600          A            100         
33600          A            100

How can I do this with as update statement?

 

[Chris Miller]

You can't really, unless you do Update Delvr_pcs=150 Where REcno()=1

This is a case for REPLACE.

But you could also change code to have a unique ID within the fields. It's a good practice to query in a way such an Id field always is part of the result.

Chriss

 

[TamarGranor]

I think it would probably be helpful if you explain the business process you're trying to implement instead of just the technique you want to use. Something isn't right here if you really have a table with all those records that can't be distinguished from each other.

Tamar

 

[Chris Miller]

To repeat what I said in the other thread about the same question: If you index on a field you want to be maximum, then SET ORDER TO index DESCENDING, the maximum is in the first record, no matter if it's a single maximum or several (or even all) records have that maximum, so it qualifies for the change. And REPLACE replaces in one record (unless you REPLACE ALL or REPLACE FOR), so that's easier than SL in this case.

You know already I second Tamar, because the way you have it the records miss something, either they miss an Id (every query result should include an id/key to be able to know which record in a real table to update, eventually) or it should be an aggregation result from a grouping that should have one row per group, but would only be able to tell you the group defining values, not the id of any individual record, as the group summing will generally be about a group of records (thus the group by). In your case it looks like a wrong grouping and the actual result should be one record with 300 (grouped by nStylecode and clotname).

Chriss

 

[Niki_S]

To do this I used like this. But I can't update my record.

SELECT _updated
INDEX ON STR(nStylecode,5,0)+cLotName+STR(Delvr_Pcs,12,2) TAG Lot_Del

SELECT _DelDtl 
SCAN 
	SELECT _updated
	IF SEEK(STR(_DelDtl.nStylecode,5,0)+_DelDtl.cLotName+STR(_DelDtl.Delvr_Pcs,12,2))
		SELECT _DelDtl 
		replace Delvr_Pcs WITH _updated.nDelvr_Pcs 
	ENDIF 
ENDSCAN

How can I fix this?

 

[Chris Miller]

There's nothing wrong there, is it? But look at the data. The seek always seeks to the first record that has the index value.
Then after you update the Delvr_Pcs there still are two more records which match the _DelDtl.Delvr_Pcs. So if you seek for the same data in the next scan loop iteration you also find one, the next record, and update it, too.

If you only want to update one record overall, don't scan at all, find the one record in _DelDtl which should be the source of data, then the one record in _updated and do the replace there.

If you want to update one record per scan iteration (per record in _DelDtl) you do that already.

Chriss

 

[Niki_S]

The issue was the length of my fields. Now it's ok. Thank you for the helps