Could not delete from specified tables. (Error 3086)

  • Follow


I have a delete query that works as select query, but will errors when 
changed to a delete query.  As part of the query I have the table I am 
deleting the records from as well as a query limits to records to be deleted. 
 The main table contains invoice attributes with a multi-field primary key 
(invoice number & revenue category).  The query may list multiple invoice 
numbers, but always unique.  So the relationship is always many to one.  I 
ran into a similar situation with update queries, but once I converted the 
limiting query into a Make Table Query and used the results of the new table 
in the Update Query it worked.  I tried converting my limit query to a Make 
Table Query and use the results, but I received the same error.  Below is the 
SQL code for the query in case it helps, but I have to warn I have no 
training in SQL, and I usually only use when given advise. 

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category] RIGHT JOIN [tblRR - Arrangement 
Adjustment Clear TEMP] ON [tblRA - Invoices by Revenue 
Category].InvoiceNumber = [tblRR - Arrangement Adjustment Clear 
TEMP].InvoiceNumber;

0
Reply Utf 1/4/2008 1:21:01 AM

On Thu, 3 Jan 2008 17:21:01 -0800, mikeycan
<mikeycan@discussions.microsoft.com> wrote:

>I have a delete query that works as select query, but will errors when 
>changed to a delete query. 

Why the right join? That will - if the query works at all - delete all the
records in your table!

If you want to delete only those records in  
[tblRA - Invoices by Revenue Category] which exist in 
[tblRR - Arrangement Adjustment Clear TEMP] try

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category] INNER JOIN [tblRR - Arrangement 
Adjustment Clear TEMP] ON [tblRA - Invoices by Revenue 
Category].InvoiceNumber = [tblRR - Arrangement Adjustment Clear 
TEMP].InvoiceNumber;

This will work only if there is a unique index on InvoiceNumber. An
alternative is to use an IN clause:

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category] 
WHERE InvoiceNumber IN
 (SELECT InvoiceNumber FROM
 [tblRR - Arrangement Adjustment Clear TEMP] );


Back everything up first of course!!!

             John W. Vinson [MVP]
0
Reply John 1/4/2008 3:16:09 AM

1 Replies
1368 Views

(page loaded in 0.127 seconds)


Reply: