Delete query with inner join

[AccessHelp123]

Hi all,

I am trying to combine a Delete query with inner join. This is what I have

DELETE tableTemp.*
FROM tableTemp INNER JOIN tableSiteStatus ON [tableTemp].[cellidentity]=[tableSiteStatus].[Cell ID]
WHERE ((([tableSiteStatus].[Final Cut-Over Date])="Yes") And (([tableTemp].[networkCC])=0));


I get an error message saying cannot delete from the specified table. Can someone help? Thanks a lot.

 

[BNPMike]

Possibly that join is non-updatable. If you view it in Datasheet View can you add/update/delete there?

 

[PHV]

You may try something like this:
DELETE FROM tableTemp
WHERE networkCC=0
AND cellidentity In (SELECT [Cell ID] FROM tableSiteStatus WHERE [Final Cut-Over Date]="Yes");

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

 

[AccessHelp123]

Thanks PHV. I tried a variety of combinations. This is the only one that seems to work. Thanks Mike but I was gonna use with a button on a form. So it needed to be a query.

 

[BNPMike]

I just meant use the datasheet to test whether it was updatable or not. PHV's method gets around possible non-updatability but then again you won't have learned why the first one didn't work...