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 Query Does Not
I have a database that won't let me delete records using a Delete Query. I have an Access 2003 database that has several tables in it that are all related to a client table and have referential integraty and the cascade delete enabled. I wish to delete all clients that have not been seen for two years or more. And yes, I really want to delete them. A "meal" table contains the last visit date. See relationships... The idea is to find all clients who have not been seen in two or more years and delete them. The data sheet view shows that the correct clients are selected. However, when I try to run the delete query it says it cannot delete the records. The "client" table is the table to which all other tables are related to. I don't care if I loose all the data for a client. If the client has not been in in two years a new record would have to be created anyway. The SQL generated is: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". Does anyone have an idea as to why this does not work and what might be done to make it work?? Thanks! |
#2
|
|||
|
|||
Delete Query Does Not
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#3
|
|||
|
|||
Delete Query Does Not
John,
You've made the assumption that cascading delete has been implemented. Ron, If you have not set up the relationships and implemented cascading deletes when you did so, then you will have to run two queries. Delete the appropriate records in the meal table Then delete records in the client table that don't have any record(s) in the meal table. John Vinson wrote: On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#4
|
|||
|
|||
Delete Query Does Not
And, if you have not implemented cascading deletes, you will know this when
you try to delete the client row without deleting the meal rows first. You'll get a message about this, but the data will not be damaged, as the delete will simply fail. At that point, either implement the cascading or use Mr. Spencer's suggestion. You should check the meal table to make sure this has worked. Have the rows for the related client row been deleted, or are they still there, orphaned? If they have not been deleted, check the client table to confirm they are truly orphaned. If so, then the relationship has not been created to prevent this. Thorough testing and knowing what to do about it is essential before going production on a thing like this. Tom Ellison "John Spencer" wrote in message ... John, You've made the assumption that cascading delete has been implemented. Ron, If you have not set up the relationships and implemented cascading deletes when you did so, then you will have to run two queries. Delete the appropriate records in the meal table Then delete records in the client table that don't have any record(s) in the meal table. John Vinson wrote: On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#5
|
|||
|
|||
Delete Query Does Not
On Sun, 05 Feb 2006 12:35:10 -0500, John Spencer
wrote: You've made the assumption that cascading delete has been implemented. Ron did say: I have an Access 2003 database that has several tables in it that are all related to a client table and have referential integraty and the cascade delete enabled. John W. Vinson[MVP] |
#6
|
|||
|
|||
Delete Query Does Not
This does not work. It selects many clients who have been in within two
years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#7
|
|||
|
|||
Delete Query Does Not
Dear Ron:
I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#8
|
|||
|
|||
Delete Query Does Not
Ouch. I completely missed that. Should have known better than to correct the wizard.
John Vinson wrote: On Sun, 05 Feb 2006 12:35:10 -0500, John Spencer wrote: You've made the assumption that cascading delete has been implemented. Ron did say: I have an Access 2003 database that has several tables in it that are all related to a client table and have referential integraty and the cascade delete enabled. John W. Vinson[MVP] |
#9
|
|||
|
|||
Delete Query Does Not
The meals table contains the client master ID, a meals master ID, a list of
date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
#10
|
|||
|
|||
Delete Query Does Not
Dear Ron:
Then it sounds like my understanding is correct. Have you tried what I proposed? Tom Ellison "Ron Le Blanc" wrote in message ... The meals table contains the client master ID, a meals master ID, a list of date, one per row, of the dates the client has visited the food bank. There can be and often are multiple entries per client. The last row contains the last date a client came into the food bank. Soooo... if that date is two or more years old I want to delete the client and all records related to this client. "Tom Ellison" wrote in message ... Dear Ron: I'll make a guess at this. Perhaps we are misunderstanding your meaning of "LastVisit." If you have several rows in [meals] each with a different LastVisit, it is possible you want to delete the client and all meals only when the most recent LastVisit is prior to 730 days ago. Is that what you want? The query as I read it would delete a client and related meals if there is ANY visit more than 730 days ago. That would be a quite different thing. It could be: SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID I have presented this as a SELECT query not a DELETE. It shows what rows would be deleted if it were changed to be a DELETE query. By not deleting anything, testing is easier, as you can change it and run it again without having to restore the data every time it deletes wrongly. To see what you are doing further, build another query around this (temporarily): SELECT * FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE client.MasterID IN ( SELECT client.MasterID FROM client INNER JOIN meals ON meals.MasterID = client.MasterID WHERE MAX(meals.LastVisit) Date() - 730 GROUP BY client.MasterID) ORDER BY client.MasterID, meals.LastVisit Such a test is revealing, is it not? I would remark that this is a potentially dangerous query. If your system date is off, you could be removing rows you do not intend. That sounds like a bit of a risk to me. Tom Ellison "Ron Le Blanc" wrote in message ... This does not work. It selects many clients who have been in within two years. It does find a bunch of the "right" clients to delete but also includes what seems be be random other clients not meeting the two years since last time they came in. "John Vinson" wrote in message ... On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc" wrote: DELETE client.*, client.MasterID, meals.LastVisit, client.f_name, client.l_name FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); The error message says: "Cannot delete from table". You're trying too hard. Don't delete anything from the Meals table - cascading deletes will take care of that. DELETE client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE (((meals.LastVisit)Date()-730)); John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete Query Doesnt | Ron Le Blanc | Running & Setting Up Queries | 7 | February 8th, 2006 01:21 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |