ARGH! Operation must use an updateable query 1

[Crowley16]

This is really getting on my nerves now!

the sql I have is like:

UPDATE tbl1 SET 
   tbl1.col1 = (SELECT tbl2.col1 
               FROM tbl2 INNER JOIN tbl3 ON tbl2.key = tbl3.key
               WHERE (tbl2.Key="someValue")
               )
WHERE (((tblGPDetails.PracticePostCode)='someValue'));

and I'm getting a: Operation must use an updateable query error...

what's going on?

 

[Crowley16]

Oops...

WHERE (((tblGPDetails.PracticePostCode)='someValue'));

shold be:

WHERE (((tbl1.key)='someValue'));

 

[PHV]

Try to replace the INNER JOIN by a WHERE EXISTS subquery(provided the tbl3 existence check is mandatory).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

PHV, I'm not sure what you mean...

the exact sql I have is:

UPDATE tblGPDetails SET 
PCTCode = (
   SELECT lnkTblGPPc.[PCG of Residence]
   FROM lnkTblGPPc INNER JOIN lnkTblPCT ON lnkTblGPPc.[PCG of Residence] = lnkTblPCT.[Organisation Code]
   WHERE (lnkTblGPPc.Postcode="AL10 8HP")
), 
PCTName = (
   SELECT lnkTblPCT.[Organisation Name]
   FROM lnkTblGPPc INNER JOIN lnkTblPCT ON lnkTblGPPc.[PCG of Residence] = lnkTblPCT.[Organisation Code]
   WHERE (lnkTblGPPc.Postcode="AL10 8HP")
), 
PCTCounty = (
   SELECT IIf(IsNull(lnkTblPCT.[Address Line 5]),lnkTblPCT.[Address Line 4],lnkTblPCT.[Address Line 5])
   FROM lnkTblGPPc INNER JOIN lnkTblPCT ON lnkTblGPPc.[PCG of Residence] = lnkTblPCT.[Organisation Code]
   WHERE (lnkTblGPPc.Postcode="AL10 8HP")
)
WHERE (((PracticePostCode)='AL10 8HP'));

I think it's the last one, PCTCounty which is causing this because the SELECT IIF(...) isn't an updateable query.
However I don't understand why the subqueries needs to be updateable since I'm not updating anything from there...

 

[PHV]

HAve you tried to replace this:
SELECT IIf(IsNull(lnkTblPCT.[Address Line 5]),lnkTblPCT.[Address Line 4],lnkTblPCT.[Address Line 5])
By something like this ?
SELECT Nz(lnkTblPCT.[Address Line 5],lnkTblPCT.[Address Line 4]) As AddressLine

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Here is an example query from Access 97 that may be helpful. It allows you to have multiple tables in the Update clause as long as you are only updating one of the tables. All tables have primary indexes.

UPDATE PackageControl,
ScreenList INNER JOIN ScreenMap ON ScreenList.Screen = ScreenMap.Screen
SET PackageControl.LOB = ScreenMap.FieldName
WHERE ScreenMap.FieldNum=1 ;

 

[Crowley16]

PHV, it still comes up with the same error

JonFer, your solution seems to work, however there's over 2 million postcodes and the update statement takes forever... but have a star anyway...

I'm giving up on doing this simply, I'll just have to do this explicitly with recordsets...

Thanks anyway...

 

[JonFer]

Are these fields indexed: Postcode, [Organisation Code], and [PCG of Residence]?

Can you post your SQL? There may be something else we can do.

 

[Crowley16]

yes, the fields are all indexed...

however the data is sitting on a backend db on a server somewhere, and it takes ages to get to it...

it's much quicker to use recordsets and I can't be bothered to work on this bit anymore...

Thanks anyway...

 

[JonFer]

Okay, you don't have to respond then but here is one last thought. If the backend DB is not Access, you could try a pass-through query which should run much faster.