Update query help VFP 6.0 1

[mapman04]

I am using VFP 6.0 and trying to update a field in a table with a value from a field in another table. Here's what I have tried:

UPDATE ACARINV.AI_GLCOST WITH SHIP.TRANS_AMT from ACARINV LEFT OUTER JOIN SHIP ON ACARINV.AI_CONO = SHIP.CO_NUMBER, ACARINV.AI_LNNO = SHIP.LN_NO,
ACARINV.AI_SHIPNO = SHIP.SHPMNT_NO

I've searched and can't find any examples as to how to do this. When I tried creating a VIEW, it told me I don't have a database open. I'm using free tables. Can anyone point me in the right direction?

Thanks,

mapman04

 

[TamarGranor]

It looks like you've mixed the two forms of UPDATE in a single command. The one you want is SQL UPDATE, but in VFP 6, it can't use joins. Your best bet is to set a relation and use the REPLACE command.

Tamar

 

[mapman04]

Can you give me an example? I'm not real fluent in Fox. Thanks for the input.

mapman04

 

[TamarGranor]

You'll need an index in Ship based on the combination of fields that link the two tables. Then:

USE Ship Order LinkingIndex IN 0
USE ACarInv IN 0
SELECT ACarInv
SET RELATION TO AI_CONO + AI_LMNO+AI_SHIPNO INTO Ship

REPLACE AI_GLCost WITH Ship.Trans_Amt ;
FOR NOT EOF("Ship") IN ACarInv

Tamar

 

[mapman04]

It doesn't like this statement. Do I have to actually create a field in the table called LinkingIndex? I've tried several scenarios without any sucess. I'm assuming I need something like:

USE Z:\FOXSYS\DATA\SHIP ORDER CO_NUMBER, LN_NO, SHPMNT_NO IN 0

I apologize for my lack of knowledge.

Thanks,

mapman04

 

[mapman04]

I was able to get this to run, but it didn't update the field in the table. Here's what I did:

In table designer, I created a canidate index called LinkIndex on the three fields that need to be linked. From the command line I issued these commands:

Use Ship Order LinkIndex in 0
Use acarinv in 0
SELECT ACARINV
SET RELATION To AI_CONO + AI_LNNO + AI_SHIPNO Into Ship
REPLACE AI_GLCost With Ship.Sum_Trans_;
For NOT EOF("SHIP") IN ACARINV

Where did I go wrong?

Thanks,

mapman04

 

[TamarGranor]

That looks right. Try issuing BROWSE after the SET RELATION to see whether you have things properly link together. Use the FIELDS clause to list fields from both tables in the Browse:

BROWSE FIELDS AI_CONO, AI_LNNO, AI_SHIPNO, Ship.CoNumber, Ship.LNno, Ship.ShpmntNo

Tamar

 

[mapman04]

When I issue the browse fields command I show the data from the acarinv table but see any data from the ship table. These columns are empty. I know that this link works in code because when you run the update program, it adds the value for the new transactions into the ai_glcost field. But I need to populate the field for all of the old transactions. I verified that the index on Ship is in the proper order. All of the index fields in both tables are character fields. They are not the same sizes though. I also checked to verify that a record in the ship table is in the acarinv table. It was. Any ideas?

Thanks,

mapman04

 

[TamarGranor]

The fields not being the same size is the problem. You need to use either the index expression or the relation expression to make it uniform. PADR() should help you out here.

Tamar