Delete Query - Delete matching records from 2 tables - Access 2000

[Chris Stammers]

Hello,

I have a query to delete records that match from the 2 tables contained.
When I view the result of the query before running, it looks like it is going
to work however when I actually come to run it, I get the error 'Could Not
Delete From Specified Tables'. After reading the Help pages, I unchecked the
box 'Open Databases using record-level locking' from the Advanced Options tab
and this hasn't helped. Is it the case that I will need to close Access for
the tables to update before the query will run properly? Here is the SQL:
DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
Expr1
FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
Retention.POLICY;

Many thanks.
Chris

 

[Allen Browne]

Perhaps a subquery would do the trick:

DELETE FROM [Daily Check]
WHERE EXISTS
(SELECT policy
FROM Retention
WHERE Retention.Policy = [Daily Check].policy);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[John Spencer (MVP)]

YOu can only delete records from ONE table at a time.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

To delete records from Daily Check the query might look like
DELETE
FROM [Daily Check]
WHERE policy in (SELECT Policy From Retention)

To delete records from Retention the query would look like
DELETE
FROM Retention
WHERE policy in (SELECT Policy From [Daily Check])

You *MIGHT* be able to get this to work. Note that only one table is
mentioned in the DELETE clause.
DELETE DISTINCTROW Retention.*
FROM [Daily Check] INNER JOIN Retention
ON [Daily Check].policy = Retention.POLICY;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Chris Stammers]

Many thanks for your help. Your last option worked fine.

Regards,
Chris

YOu can only delete records from ONE table at a time.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

To delete records from Daily Check the query might look like
DELETE
FROM [Daily Check]
WHERE policy in (SELECT Policy From Retention)

To delete records from Retention the query would look like
DELETE
FROM Retention
WHERE policy in (SELECT Policy From [Daily Check])

You *MIGHT* be able to get this to work. Note that only one table is
mentioned in the DELETE clause.
DELETE DISTINCTROW Retention.*
FROM [Daily Check] INNER JOIN Retention
ON [Daily Check].policy = Retention.POLICY;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I have a query to delete records that match from the 2 tables contained.
When I view the result of the query before running, it looks like it is going
to work however when I actually come to run it, I get the error 'Could Not
Delete From Specified Tables'. After reading the Help pages, I unchecked the
box 'Open Databases using record-level locking' from the Advanced Options tab
and this hasn't helped. Is it the case that I will need to close Access for
the tables to update before the query will run properly? Here is the SQL:
DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
Expr1
FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
Retention.POLICY;

Many thanks.
Chris

 

[Chris Stammers]

Thanks for your help with this.

Regards,
Chris

Perhaps a subquery would do the trick:

DELETE FROM [Daily Check]
WHERE EXISTS
(SELECT policy
FROM Retention
WHERE Retention.Policy = [Daily Check].policy);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a query to delete records that match from the 2 tables contained.
When I view the result of the query before running, it looks like it is
going
to work however when I actually come to run it, I get the error 'Could Not
Delete From Specified Tables'. After reading the Help pages, I unchecked
the
box 'Open Databases using record-level locking' from the Advanced Options
tab
and this hasn't helped. Is it the case that I will need to close Access
for
the tables to update before the query will run properly? Here is the SQL:
DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
Expr1
FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
Retention.POLICY;

Many thanks.
Chris