DELETE: What's wrong with this?

[Dave]

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

Using Northwind, when I type the above into the SQL window, I get the
message "Specify the table containing the records you want to delete."

I get the same error with:

DELETE *

If I use:

DELETE o.*, od.*

I get the error "Could not delete from specified tables."

HELP indicates the database tables are read only. They are not. I can get
the delete to work with a subquery.

How do I write a DELETE in Access that uses a JOIN?

 

[Van T. Dinh]

I may be wrong but I don't think you can delete the "One" Record in the
Query that involves the Tables in the One-to-Many relationship.

Logically, we delete Records from ONE Table at a time (possibly with
Cascade-Delete effect). In fact, JET SQL is the only SQL flavour that
allows deletion from a Query that uses joins. Most other SQL flavours don't
allow this.

 

[Dave]

Logically, we delete Records from ONE Table at a time (possibly with

Cascade-Delete effect). In fact, JET SQL is the only SQL flavour that
allows deletion from a Query that uses joins. Most other SQL flavours don't
allow this.

The delete query below will work fine against the SQL Server Northwind
database using T-SQL...

DELETE FROM orders
FROM Orders AS o INNER JOIN [order details] AS od ON rderID
=od.OrderID
WHERE od.ProductID=2

...provided I drop the constraint first....
ALTER TABLE [order details]
DROP CONSTRAINT FK_Order_Details_Orders

In fact, if I create a linked server in SQL Server to the Access Northwinds
mdb file called ACCESSNORTHWIND, the following will execute from within
QueryAnalyzer and delete 44 records in the Access Northwind database:

DELETE FROM AccessNorthwind...orders
FROM AccessNorthwind...Orders AS o INNER JOIN AccessNorthwind...[order
details] AS od ON rderID =
od.OrderID
WHERE od.ProductID=2

(Note that the cascade delete option is on for Orders-Order Details
relationship in Access Northwind)

Perhaps the Jet engine translates the T-SQL join into something else for
execution in Access. Perhaps a subquery. As I mentioned, I can get a
subquery to work in Access. This executes fine:

DELETE orders.OrderID, *
FROM orders
WHERE (((orders.OrderID) In (Select OrderId from [order details] where
productid=2)));

But my question is, can I write the same query in Access using a JOIN?

I may be wrong but I don't think you can delete the "One" Record in the
Query that involves the Tables in the One-to-Many relationship.

Logically, we delete Records from ONE Table at a time (possibly with
Cascade-Delete effect). In fact, JET SQL is the only SQL flavour that
allows deletion from a Query that uses joins. Most other SQL flavours don't
allow this.

--
HTH
Van T. Dinh
MVP (Access)

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

Using Northwind, when I type the above into the SQL window, I get the
message "Specify the table containing the records you want to delete."

I get the same error with:

DELETE *

If I use:

DELETE o.*, od.*

I get the error "Could not delete from specified tables."

HELP indicates the database tables are read only. They are not. I can get
the delete to work with a subquery.

How do I write a DELETE in Access that uses a JOIN?

 

[Van T. Dinh]

If you look at the T-SQL Strings you have 2 FROM clauses and you delete with
the 1st FROM clause. There is no equivalent of the 2nd FROM clause in JET
SQL.

I did a quick test with JET SQL and I could only delete the "Many" Records,
NOT the "One" Records.

You can also use EXISTS in your case to delete the "One" Record.



Unfortunately, JET SQL and T-SQL look similar but they behave differently.
For example, I have these SQLs in T-SQL (both work correctly):

****SQL1****
UPDATE tblInProcessData
SET frg_ProdSpecNameID = PS.[frg_ProdSpecName]
FROM tblInProcessData IP_S INNER JOIN
tblProdSpec PS ON IP_S.frg_SpecRefID = PS.SpecRefID
WHERE (frg_ProdSpecNameID Is Null) AND
(PS.frg_ProdSpecName Is Not Null)

****SQL2****
UPDATE tblInProcessData
SET frg_ProdSpecNameID = PS.[frg_ProdSpecName]
FROM tblInProcessData IP_S INNER JOIN
tblProdSpec PS ON IP_S.frg_SpecRefID = PS.SpecRefID
WHERE (InProcTestID = IP_S.InProcTestID) AND
(frg_ProdSpecNameID Is Null) AND
(PS.frg_ProdSpecName Is Not Null)

The difference between SQL1 and SQL2 is that in SQL2,
there is an explicit link between the Table
tblInProcessData and the second copy of the same Table
(alias [IP_S]). In SQL1, there is no such link but the
SQL Server obviously associates a Record from the
tblInProcessData to the corresponding Record in the 2nd
copy of the same Table (alias [IP_S]) correctly.

IIRC, only SQL2 (explicit link between 2 copies of tblInProcData) works
correctly in JET SQL.

--
HTH
Van T. Dinh
MVP (Access)

Logically, we delete Records from ONE Table at a time (possibly with
Cascade-Delete effect). In fact, JET SQL is the only SQL flavour that
allows deletion from a Query that uses joins. Most other SQL flavours don't
allow this.

The delete query below will work fine against the SQL Server Northwind
database using T-SQL...

DELETE FROM orders
FROM Orders AS o INNER JOIN [order details] AS od ON rderID
=od.OrderID
WHERE od.ProductID=2

..provided I drop the constraint first....
ALTER TABLE [order details]
DROP CONSTRAINT FK_Order_Details_Orders

In fact, if I create a linked server in SQL Server to the Access Northwinds
mdb file called ACCESSNORTHWIND, the following will execute from within
QueryAnalyzer and delete 44 records in the Access Northwind database:

DELETE FROM AccessNorthwind...orders
FROM AccessNorthwind...Orders AS o INNER JOIN AccessNorthwind...[order
details] AS od ON rderID =
od.OrderID
WHERE od.ProductID=2

(Note that the cascade delete option is on for Orders-Order Details
relationship in Access Northwind)

Perhaps the Jet engine translates the T-SQL join into something else for
execution in Access. Perhaps a subquery. As I mentioned, I can get a
subquery to work in Access. This executes fine:

DELETE orders.OrderID, *
FROM orders
WHERE (((orders.OrderID) In (Select OrderId from [order details] where
productid=2)));

But my question is, can I write the same query in Access using a JOIN?

 

[Gary Walter]

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

Using Northwind, when I type the above into the SQL window, I get the
message "Specify the table containing the records you want to delete."

I get the same error with:

DELETE *

If I use:

DELETE o.*, od.*

I get the error "Could not delete from specified tables."

HELP indicates the database tables are read only. They are not. I can get
the delete to work with a subquery.

How do I write a DELETE in Access that uses a JOIN?

Hi Dave,

I have enjoyed this discussion and don't mean to butt in,
but this is an important KB I believe:

ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

*******quote******************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft Access.
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new delete query
in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click Properties.
Click an empty area in the upper half of the query window so that the property sheet
displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
*******unquote****************************************

(I did not test, but believe this is what you want)

DELETE DISTINCTROW o.*
FROM Orders AS o
INNER JOIN
[order details] AS od
ON rderID = od.OrderID
WHERE od.ProductID=2;

Apologies again for butting in,

Gary Walter

 

[Dave]

Thank you very much Gary

Yes, adding the DISTINCTROW to the DELETE clause as below will allow the
query to execute properly.

DELETE DISTINCTROW o.*, od.ProductID
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE (((od.ProductID)=1));

That's good to know.

Two notes:

1. Access automatically adds the ", od.ProductID" to the DELETE clause but
the query will still execute without it.

2. My copy of the Northwind database had the cascade deletes turned on for
the relationship between orders and order details. If this is turned off,
you will get a FK violation when you try to delete orders with related
records in order detail.

Thanks again Gary for pointing out this detail.

Dave

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

Using Northwind, when I type the above into the SQL window, I get the
message "Specify the table containing the records you want to delete."

I get the same error with:

DELETE *

If I use:

DELETE o.*, od.*

I get the error "Could not delete from specified tables."

HELP indicates the database tables are read only. They are not. I can get
the delete to work with a subquery.

How do I write a DELETE in Access that uses a JOIN?

Hi Dave,

I have enjoyed this discussion and don't mean to butt in,
but this is an important KB I believe:

ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

*******quote******************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft Access.
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new delete query
in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click Properties.
Click an empty area in the upper half of the query window so that the property sheet
displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
*******unquote****************************************

(I did not test, but believe this is what you want)

DELETE DISTINCTROW o.*
FROM Orders AS o
INNER JOIN
[order details] AS od
ON rderID = od.OrderID
WHERE od.ProductID=2;

Apologies again for butting in,

Gary Walter

 

[Van T. Dinh]

Thanks, Gary.

--
HTH
Van T. Dinh
MVP (Access)

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

Using Northwind, when I type the above into the SQL window, I get the
message "Specify the table containing the records you want to delete."

I get the same error with:

DELETE *

If I use:

DELETE o.*, od.*

I get the error "Could not delete from specified tables."

HELP indicates the database tables are read only. They are not. I can get
the delete to work with a subquery.

How do I write a DELETE in Access that uses a JOIN?

Hi Dave,

I have enjoyed this discussion and don't mean to butt in,
but this is an important KB I believe:

ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

*******quote******************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft Access.
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new delete query
in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click Properties.
Click an empty area in the upper half of the query window so that the property sheet
displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
*******unquote****************************************

(I did not test, but believe this is what you want)

DELETE DISTINCTROW o.*
FROM Orders AS o
INNER JOIN
[order details] AS od
ON rderID = od.OrderID
WHERE od.ProductID=2;

Apologies again for butting in,

Gary Walter