Problem with Delete query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Devenish - 31 Jan 2006 16:13 GMT

I have two tables InputData and CommonData each with three fields F1
(Long integer), F2 (Date/Time), F3 (Text) i.e. the tables have
identical design. I am trying to delete all rows from InputData which
have their F1 value in CommonData as well. This means I wish to leave
in InputData only those rows which are not also in CommonData

When I try to use the Sql:
Delete InputData.* From InputData
Inner Join CommonData On InputData.F1 = CommonData.F1

I get the error message:
'Could not delete from specified tables'

Both tables are local and are not in use by any other form or query.

Can I delete these commmon rows? and if so, how?

Reply to this Message

Allen Browne - 31 Jan 2006 16:26 GMT

Try a subquery:

DELETE FROM InputData
WHERE EXISTS
(SELECT F1 FROM CommonData
WHERE CommonData.F1 = InputData.F1);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have two tables InputData and CommonData each with three fields F1
> (Long integer), F2 (Date/Time), F3 (Text) i.e. the tables have
[quoted text clipped - 12 lines]
>
> Can I delete these commmon rows? and if so, how?

Reply to this Message