How to convert update statement into Replace command in foxpro

[Niki_S]

This is my update statement and I want to convert it into a replace command.

SELECT UPDT 
SCAN 
	UPDATE Delshdvl SET Pcs=UPDT.Pcs ;
	WHERE Pln_Cd=UPDT.Pln_Cd AND Shp_Style=UPDT.Shp_Style AND Lot=UPDT.Lot 
ENDSCAN

How can do this?

Thank you

 

[GriffMG]

There are a lot of ways to do this, I prefer

SELECT UPDT
SCAN
	** SELECT THE TARGET REPLACE WORKAREA
	SELECT DELSHDVL
	REPLACE PCS WITH UPDT.PCS FOR PLN_CD=UPDT.PLN_CD .AND. SHP_STYLE=UPDT.SHP_STYLE .AND. LOT=UPDT.LOT
	** SCAN WILL RESELECT UPDT
ENDSCAN

But it could be suggested that I am wasting a SELECT statement, this should work:

SELECT UPDT
SCAN
	REPLACE DELSHDVL.PCS WITH UPDT.PCS FOR DELSHDVL.PLN_CD=UPDT.PLN_CD .AND. DELSHDVL.SHP_STYLE=UPDT.SHP_STYLE .AND. DELSHDVL.LOT=UPDT.LOT
ENDSCAN

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. ​

 

[Mike Lewis]

But it could be suggested that I am wasting a SELECT statement

Griff, you're right. There is no need to do the SELECT with the SCAN loop. VFP does that for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Thanks Mike.

A thought, ENDSCAN would support further clarity if there was a deep nesting of some kind

Like

SELECT UPDT
SCAN &&UPDT
    .. do something
ENDSCAN && UPDT

Rather like a for next loop


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. ​

 

[Mike Lewis]

But the whole thing can be rewritten to get rid of the SCAN / ENDSCAN. I'm in a rush just now, but will do that later - although I expect someone will get there before me.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

An index, or two, could make it more efficient...

SELECT DELSHDVL
INDEX ON PLN_CD+SHP_STYLE+LOT TAG DELSHDVL
SELECT UPDT
SCAN && UPDT
	SELECT DELSHDVL
	SET ORDER TO DELSHDVL
	**  PLN_CD+SHP_STYLE+LOT
	SEEK (UPDT.PLN_CD+UPDT.SHP_STYLE+UPDT.LOT)
	DO WHILE .NOT. EOF() .AND. PLN_CD+SHP_STYLE+LOT = (UPDT.PLN_CD+UPDT.SHP_STYLE+UPDT.LOT)
		REPLACE DELSHDVL.PCS WITH UPDT.PCS 
		SKIP
	ENDDO
ENDSCAN && UPDT

Or if you prefer SCAN/ENDSCAN

SELECT DELSHDVL
INDEX ON PLN_CD+SHP_STYLE+LOT TAG DELSHDVL
SELECT UPDT
SCAN && UPDT
	SELECT DELSHDVL
	SET ORDER TO DELSHDVL
	**  PLN_CD+SHP_STYLE+LOT
	SEEK (UPDT.PLN_CD+UPDT.SHP_STYLE+UPDT.LOT)
	SCAN WHILE PLN_CD+SHP_STYLE+LOT = (UPDT.PLN_CD+UPDT.SHP_STYLE+UPDT.LOT) && DELSHDVL
		REPLACE DELSHDVL.PCS WITH UPDT.PCS 
	ENDSCAN && DELSHDVL
ENDSCAN && UPDT

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. ​

 

[Chris Miller]

I would go the other route, change this SCAN..ENDSCAN to a single UPDATE-SQL:

UPDATE Delshdvl SET Pcs=UPDT.Pcs FROM Delshdvl ;
INNER JOIN UPDT ON Pln_Cd=UPDT.Pln_Cd AND Shp_Style=UPDT.Shp_Style AND Lot=UPDT.Lot

Chriss

 

[Mike Lewis]

Chriss, that's exactly what I had in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

I tried in this way,

UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
INNER JOIN UPDT ON Style=UPDT.Style AND nStylecode=UPDT.nStylecode AND cLotName=UPDT.cLotName

But it says "STYLE is not unique and must be qualified".

 

[GriffMG]

Maybe change it thusish:

UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
INNER JOIN UPDT ON _DelDtl.Style=UPDT.Style AND nStylecode=UPDT.nStylecode AND cLotName=UPDT.cLotName

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]

Thank you for the helps. Now it's working

 

[Chris Miller]

thusish, good.

Well "STYLE is not unique" menas it's a field of both tables "must be qualified" means the name must be extended, therefore, to become explicitly meaning the style field of the table you need.

And I see you got that working. I assume your update now is:

UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
INNER JOIN UPDT ON _DelDtl.Style=UPDT.Style AND _DelDtl.nStylecode=UPDT.nStylecode AND _DelDtl.cLotName=UPDT.cLotName

I assumed a field name without table prefix would be assumedto mean the field of the main table you update and that's firstin the FROM clause.

And just to complete this: Thiskind of update is called correlated update, correlated to another table with a subquery, and is only possible with VFP9s extended sql engine. It's mentioned in the chapter SL Language Improvements:

https://vfphelp.com/vfp9/html/6c75fbec-8d4a-4809-a521-c54802e59ea5.htm

That's not saying you should have found it, but I want to point out using SQL makes more sense even on the VFP side with all it's new possibilities, and especially to you, Niki, as you work with an SQL Server backend and so practicing SQL will always improve your usage of both VFP and SQL Server and the combination of both.

Chriss

 

[Rick C. Hodgin]

You can use the old fashioned way:

*SELECT updt
*SCAN
*    UPDATE Delshdvl ;
*        SET Pcs=UPDT.Pcs ;
*        WHERE   Pln_Cd    = UPDT.Pln_Cd ;
*            AND Shp_Style = UPDT.Shp_Style ;
*            AND Lot       = UPDT.Lot
*
*ENDSCAN

#define theKey Pln_Cd + Shp_Style + Lot
SELECT updt
INDEX ON theKey TAG tmpKey

SELECT Delshdvl
SET RELATION TO theKey INTO updt
REPLACE ALL pcs WITH updt.pcs FOR NOT EOF("updt")

SET RELATION OFF INTO updt

SELECT updt
DELETE TAG tmpKey
* And if you do this regularly, you can define the key and not have to create it or delete it

The relationship will automatically advance the pointer into updt each time the record in delshdvl changes. So it's constantly doing the SEEK for you automatically behind the scenes.

This kind of XBASE equivalent command lets you debug / step through your records using a SCAN .. ENDSCAN with a single REPLACE instead of a REPLACE ALL, and you can examine each one to see why it's working or not working.

Very powerful debugging tools in learning XBASE commands, and they're not that much slower than more modern SQL equivalents.

Note: This is the meat of the algorithm:

#define theKey Pln_Cd + Shp_Style + Lot
SELECT Delshdvl
SET RELATION TO theKey INTO updt
REPLACE ALL pcs WITH updt.pcs FOR NOT EOF("updt")

The rest just supports this portion.

--
Rick C. Hodgin