Delete Query problem:Must be Updateable query 1

[SUnderwood]

Hello. Happy weekend!

Could someone please look over this SQL statement and provide some clue as to why MS Access gives me the error "Operation must use an updateable query"

--snip start--
DELETE events.*, events.MPAN, events.Regi_Date, events.DFlow, events.Event_Date
FROM events, sessiondetails
WHERE (((events.MPAN)=[sessiondetails].[mpan]) AND ((events.Regi_Date)=[sessiondetails].[regi_date]) AND ((events.DFlow)=[sessiondetails].[dflow]) AND ((events.Event_Date)=[sessiondetails].[event_date]));
--snip end--


Sean

 

[cheerio]

As a general point a query will only be updateable if Access can guarantee to identify the right record and this depends on the key fields.

More specifically I find that Access prefers to be absolutely clear what table you are trying to delete from and joins create confusion. In practical terms that means using a subquery rather than a join. So I would try something like:

DELETE *
FROM events
WHERE EXISTS 
(
SELECT * 
FROM sessiondetails
WHERE events.MPAN=sessiondetails.mpan 
  AND events.Regi_Date=sessiondetails.regi_date
  AND events.DFlow=sessiondetails.dflow
  AND events.Event_Date=sessiondetails.event_date
)

 

[SUnderwood]

THAT'S THE BISCUIT!!!

Ofcourse! I neglected to remember that I was using PK fields so if there WAS a match there would be only ONE record, hence why the EXIST operator works!

At this point Hommer would say DOUH! Have a STAR for brilliant!

Cheerio, Cheerio!

 

[cheerio]

Glad to be of help.

 

[SUnderwood]

HuHA! Your help proves useful AGAIN ! I keep falling into this trap!

Is there a problems with JET? I would have thought that the following SQL would delete appropriate records from TableA, not no! We get that nasty error.

DELETE TableA.*
FROM TableA, TableB
WHERE TableA.Prop1 = TableB.Prop1
AND TableA.prop2 = TableB.Prop2;

Well thanks again. I'll try and give another Star ;-)

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com