If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DELETE: What's wrong with this?
DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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? |
#2
|
|||
|
|||
DELETE: What's wrong with this?
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) "Dave" wrote in message ... DELETE o.orderid,* FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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? |
#3
|
|||
|
|||
DELETE: What's wrong with this?
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 o.OrderID =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 o.OrderID = 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? "Van T. Dinh" wrote in message ... 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) "Dave" wrote in message ... DELETE o.orderid,* FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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? |
#4
|
|||
|
|||
DELETE: What's wrong with this?
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) "Dave" wrote in message ... 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 o.OrderID =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 o.OrderID = 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? |
#5
|
|||
|
|||
DELETE: What's wrong with this?
"Dave" wrote in message ... DELETE o.orderid,* FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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...b;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 o.OrderID = od.OrderID WHERE od.ProductID=2; Apologies again for butting in, Gary Walter |
#6
|
|||
|
|||
DELETE: What's wrong with this?
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 o.OrderID = 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 "Gary Walter" wrote in message ... "Dave" wrote in message ... DELETE o.orderid,* FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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...b;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 o.OrderID = od.OrderID WHERE od.ProductID=2; Apologies again for butting in, Gary Walter |
#7
|
|||
|
|||
DELETE: What's wrong with this?
Thanks, Gary.
-- HTH Van T. Dinh MVP (Access) "Gary Walter" wrote in message ... "Dave" wrote in message ... DELETE o.orderid,* FROM Orders AS o INNER JOIN [order details] AS od ON o.OrderID = 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...b;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 o.OrderID = od.OrderID WHERE od.ProductID=2; Apologies again for butting in, Gary Walter |
Thread Tools | |
Display Modes | |
|
|