I have a text file that is linked my database. What I'm trying to do is update another with the data from this linked file using an inner join.

I get the error message 'Updating linked table is not supported by this ISAM'. BUT I am not updating the linked file I am updating the other table, is there a way to work around (please keep in mind performance is everything)

Linked file: IIB_XREF
Other table: GRID_XREF

Query:

UPDATE GRID_XREF
INNER JOIN IIB_XREF2 ON IIB_XREF2.ALTERN_CODE = GRID_XREF.ALTERN_CODE
SET GRID_XREF.GRID_ID = IIB_XREF2.GRID_ID
WHERE IIB_XREF2.GRID_ID<>GRID_XREF.GRID_ID;


Anyone ?

You need to pull the data into a local table first, then run the update from the local table.

Create the table.

Then in your code:
Run a delete query on it to make sure there is no data in it.
Append the data from the text file.
Do the update.
Run the Delete query again to empty the table.

In any event, you want to change the local table's ID field (Can you do this?) to the text file's ID field value in each case where they are not equal? Won't this get stuck in an infinite loop?

Did any of that help?


mmcdonal

Thx for your reply I hope using a temporary table will not decrease the performance to much

Note: the grid_id is not the id/autonumber of the table, it is not a primary key it is some kind of code

How many records in the text file, and how often is this done? That is your determining factor. Otherwise the queries are nto abig deal.


mmcdonal