Update Select: Operation Must Use an Updateable Query

[aball65]

Can someone please help with the following query/error:



update EXCEL_IEMATRIX_NULLS set EXCEL_IEMATRIX_NULLS.pyld_id = (select PYLD.pyld_id from PYLD inner join EXCEL_IEMATRIX_NULLS on PYLD.pyld=EXCEL_IEMATRIX_NULLS.pyld);

Operation must use an updateable query.

Thanks

[RayMilhon]

Exactly what are you trying to do. Your SQL is attempting to update a field with a recordset. Won't work.

[aball65]

I'm migrating MS Excel spreadsheet data to MS Access. I've imported the Excel data into a temporary Access table called EXCEL_IEMATRIX_NULLS and am now trying to update this table with the foreign keys. For example, the EXCEL_IEMATRIX_NULLS table contains a field called "pyld" with various values (e.g., Encounter). My Access database has a "PYLD" table which stores all possible pyld values (e.g., Encounter, Allergy, etc.) along with each values primary key. I'm trying to update the various EXCEL_IEMATRIX_NULLS table fields with their associated primary keys. Once I do this, I'm going to create an Access table based on a "select into" command. Does that make sense?

[RayMilhon]

Try this: add a new field to your temp table call it newpyld_id and run the following query. Once it runs and you've validated the results delete the old pyld_id column and rename the newpyld_id column to pyld_id.

Update EXCEL_IEMATRIX_NULLS set EXCEL_IEMATRIX_NULLS.newpyld_id = (Select PYLD.pyld_ID from PYLD Inner join PYLD.Description = EXCEL_IEMATRIX_NULLS.pyld_id):

[aball65]

I had to make some syntax changes and replaced your description column with my pyld column:

Update EXCEL_IEMATRIX_NULLS set EXCEL_IEMATRIX_NULLS.newpyld_id = (Select PYLD.pyld_ID from PYLD Inner join EXCEL_IEMATRIX_NULLS on PYLD.pyld = EXCEL_IEMATRIX_NULLS.pyld);

The above statement raises the same error.

[aball65]

The following worked for me:

update (select * from EXCEL_IEMATRIX_NULLS inner join PYLD on EXCEL_IEMATRIX_NULLS.pyld = PYLD.pyld) set EXCEL_IEMATRIX_NULLS.pyld_id = PYLD.pyld_id;