Error: "Operation must use an updatable query."

[denveromlp]

I've been getting the following error in a delete query.

This is the SQL:

DELETE tbl_missingroad nummaster.*
FROM qry_missingflee ts INNER JOIN (tbl_missingroa dnummaster INNER JOIN tbl_locodata ON tbl_missingroad nummaster.ROAD_ NUMBER = tbl_locodata.RO AD_NUMBER) ON qry_missingflee ts.FLEET_NAME = tbl_locodata.FL EET_NAME;


Ever run into this before?
Thanks!

[PianoMan64]

@denveromlp
Hello denveromlp,

Since you're trying to link acrossed multiple table, this is a feature that isn't allowed at all. You'll have to delete each table with a set of conditions so you don't delete every record.

But once you join a table, it makes it READ ONLY.

Sorry I couldn't be of more help. Without any more detail that you have, it's kind of hard to guess.

Hope that helps,

Joe P.

[OldBirdman]

If these tables were linked using Tools->Relationships. .. and "Enforce Referential Integrity were checked, you could then click "Cascade Delete Related Records".

Deleting the table on the "One" side of a "One-to-Many" relationship will also delete the records on the "Many' side.

OldBirdman

[denveromlp]

OK, so joining tables disallows you to complete a delete query.

I'm trying to delete records contained in the table "tbl_missingfle ets" from the table "tbl_missingroa dnummaster". Now I'm trying the following SQL, but it wants me to "specify the table containing the reconds I want to delete." How do I do that?

DELETE tbl_missingroad nummaster.ROAD_ NUMBER, tbl_missingroad nummaster.FLEET _NAME, tbl_missingroad nummaster.DOWNL OAD_DATE, tbl_missingroad nummaster.MOTOR _MWHRS
FROM tbl_missingroad nummaster, tbl_missingflee ts
WHERE (((tbl_missingr oadnummaster.RO AD_NUMBER)=[tbl_missingflee ts]![FLEET_NAME]) AND ((tbl_missingro adnummaster.FLE ET_NAME)=[tbl_missingflee ts]![FLEET_NAME]) AND ((tbl_missingro adnummaster.DOW NLOAD_DATE)=[tbl_missingflee ts]![FLEET_NAME]) AND ((tbl_missingro adnummaster.MOT OR_MWHRS)=[tbl_missingflee ts]![FLEET_NAME]));

...one way I've found to accomplish this is to run an update query for tbl_missingroad nummaster and set all the records common to both tables to null, then create a delete query to delete all null records from tbl_missingroad nummaster, just curious if there is a way to do this in one query

[ChipR]

Take a look at the examples here:
SQL: EXISTS Condition

[denveromlp]

EXIST function worked great, thanks!