Delete Query One to Many

[Erika]

I have 2 tables - partial order and backorder file1. I want to remove all
order numbers on the partial order table from the backorder file1 table. I
looked at the SQL view and this is what I have

DELETE Back_order_file1.*, [Partial Back order].[Order Number]
FROM Back_order_file1 RIGHT JOIN [Partial Back order] ON
Back_order_file1.[Order Number] = [Partial Back order].[Order Number];

What am I missing? I get an error that it can not delete from specified
table.

 

[John Spencer]

You can try the following which will probably work

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.

DELETE DISTINCTROW Back_order_file1.*
FROM Back_order_file1 INNER JOIN [Partial Back order]
ON Back_order_file1.[Order Number] = [Partial Back order].[Order Number];

This one will work
DELETE FROM Back_Order_File1
WHERE Back_order_file1.[Order Number] in
(SELECT [Partial Back order].[Order Number]
FROM [Partial Back order])



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County