By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Creating Delete Query Where Related Data is Null or Empty on the Other Table

P: 52
How can make a Delete query based on two tables. Lets say the primary table where I want to make deletion is "Customers" and then the related table is "CustomerOrders". I want to delete the data from CustomerOrders which are not found on Customers table. Meaning those previous customers that are deleted on the "Customers" table have existing records on "CustomerOrders" table. ( I did not used the referential integrity previously)

I want to have Referential Integrity so that whatever changes I made on the CustomerID (this is by the way is the primary key on the Customers table and is used as a link to the CustomerOrders table) on the Customers table will be reflected on the CustomerID of the CustomerOrders table.
Aug 15 '06 #1
Share this Question
Share on Google+
5 Replies


P: 15
As far as i understood ur problem, when u deleate a coustomer on coustomer table, u want to delete his related records in the orders table. this can be done easily if u delete the orders first and the relevent coustomer later. for this u should create a delete query for the orders table and in the criteria of coustomer, u should provide the name of coustomer u are about to delete. this will delete the records and then use another delete query to delete the coustomer from table with same criteria.
in case if u have deleted coustomers first and then u want to delete their records, i can not help u. sorry
Aug 18 '06 #2

P: 52
Thanks for your reply.

Yes I deleted the customers (on the "Customer" table) first thus creating an "orphaned" data on the "CustomerOrders" table. Orphaned data becomes a "not-related" data. That time I'm not implementing "Referential Integrity". Everytime I delete a customer in the "Customer" table I'm leaving "orphaned" data on the "Orders" table.

Now I want to implement Referential Integrity. But I cannot do that unless I delete first those orphaned data on "CustomerOrders" table. And I want to create a delete query to do that.
Aug 19 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Create a query in design view. Add both tables. Join the tables on CustomerID. I assume you have a foreign key of CustomerID in the CustomerOrders table.

Double click on the line joining the two tables and choose the option to show all records in CustomerOrders and Only those records in Customer where the join fields are equal.

Show all the CustomerOrders columns in the query and only the CustomerID Primary Key of the Customer table.

Then in the Criteria section under the column for the Primary key of the Customer table (i.e. CustomerID) put Is Null. Untick the show box.

Change the query type to a delete query.

This should delete any records in CustomerOrders that don't have a related Customer record.
Aug 19 '06 #4

P: 52
Yes I did exactly like that before I ask this question in this forum.

When I did run that query, It gives an error message:

"Specify the table containing the records you want to delete".

Where would I fix this? (I'm thinking of doing it on SQL view and code the statements but I'm not yet good at coding SQL statements)

I thought it's not possible to use two tables in a delete query that's why I asked this question in this forum or maybe there's another way.

Is there something I should do in the delete query to make it run. In the delete query design view, I cannot change the row that says "Where" or "From".
Aug 19 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Pull the records for Customer Orders down using the asterisk

When you change the query design to a delete query you should see on the delete line under the CustomerOrders.* column the word From and under the CustomerID in Customer table the word Where.

You should only have two visible columns in your final query although one of them represents all the fields in the CustomerOrders table.


Yes I did exactly like that before I ask this question in this forum.

When I did run that query, It gives an error message:

"Specify the table containing the records you want to delete".

Where would I fix this? (I'm thinking of doing it on SQL view and code the statements but I'm not yet good at coding SQL statements)

I thought it's not possible to use two tables in a delete query that's why I asked this question in this forum or maybe there's another way.

Is there something I should do in the delete query to make it run. In the delete query design view, I cannot change the row that says "Where" or "From".
Aug 19 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.