Is this delete query right?

[Isaac]

In sql server it is a little more clear because i might do:

delete tablename
from
[and go on to specify a join, perhaps, involving multiple tables]

...which would only delete from "tablename"

But in access I want to make sure I got this right. Here is my delete query. I want to delete ONLY from Backend_FeedbackCentral. Nowhere else.

DELETE * from BackEnd_FeedbackCentral 
INNER JOIN [qryTopicBus in BEFC not in BID] ON (BackEnd_FeedbackCentral.[Bus Unit] = [qryTopicBus in BEFC not in BID].[Bus Unit]) AND (BackEnd_FeedbackCentral.[LRR Topic] = [qryTopicBus in BEFC not in BID].[LRR Topic]);

 

[jdraw]

If, before executing a DELETE query, you create a SELECT query with similar criteria, you can assess whether this selected set is what you want to delete.

 

[Isaac]

Great point - that much, I have already done. I ran the Select version, of the joins etc., I get 39 records, which is what I want to delete. I changed it to a delete query and just wanted to ensure only the one table was going to be deleted-from.

 

[Isaac]

I just want to make sure it's not going to be deleted from any tables involved in that query - other tables. Maybe I am over worrying here!

 

[theDBguy]

Hi. I think Access will only delete from one table source, that's why sometimes you get an error to specify the table's name.

 

[jdraw]

It should only delete from the one table-- unless there are related records in another table and Cascade Delete is set.

 

[Isaac]

Right-on, ok gentlemen, thanks a lot! Have a wonderful rest of your Thursday.

 

[isladogs]

That's exactly what will happen.
The best way to check is to start with a SELECT query and, when you're happy with the results, change it to the DELETE query

 

[Isaac]

OK, access is now telling me as you predicted, specify the table you want to delete.

But how? Seem like I already have?

 

[theDBguy]

OK, access is now telling me as you predicted, specify the table you want to delete.

But how?

View attachment 86806

Is the SQL statement the one you posted earlier?

PS. I just realized you're joining your table with a query. Perhaps there's some confusion happening when involving that query, which probably involves multiple tables.

Perhaps the best approach is to use an In() clause instead.

 

[Minty]

The Syntax in Access is exactly as you described earlier?

strSQL = "Delete * FROM YourTable"
db.Execute strSQL

What is yours NOW in the SQL window?

 

[Isaac]

The Syntax in Access is exactly as you described earlier?

strSQL = "Delete * FROM YourTable"
db.Execute strSQL

What is yours NOW in the SQL window?

DELETE BackEnd_FeedbackCentral.*, *
FROM BackEnd_FeedbackCentral INNER JOIN [qryTopicBus in BEFC not in BID] ON (BackEnd_FeedbackCentral.[LRR Topic] = [qryTopicBus in BEFC not in BID].[LRR Topic]) AND (BackEnd_FeedbackCentral.[Bus Unit] = [qryTopicBus in BEFC not in BID].[Bus Unit]);

 

[Isaac]

and if I remove everything from the results grid, my sql becomes this, which gives me same message:

DELETE *
FROM BackEnd_FeedbackCentral INNER JOIN [qryTopicBus in BEFC not in BID] ON (BackEnd_FeedbackCentral.[LRR Topic] = [qryTopicBus in BEFC not in BID].[LRR Topic]) AND (BackEnd_FeedbackCentral.[Bus Unit] = [qryTopicBus in BEFC not in BID].[Bus Unit]);

 

[theDBguy]

DELETE BackEnd_FeedbackCentral.*, *
FROM BackEnd_FeedbackCentral INNER JOIN [qryTopicBus in BEFC not in BID] ON (BackEnd_FeedbackCentral.[LRR Topic] = [qryTopicBus in BEFC not in BID].[LRR Topic]) AND (BackEnd_FeedbackCentral.[Bus Unit] = [qryTopicBus in BEFC not in BID].[Bus Unit]);

Okay, that's not the same SQL you posted earlier. Looks like there's an extra star/asterisk in it. Try taking it out.

 

[Isaac]

I think you're right, I've made some changes and got confused.

I made the SQL view be this exactly:

DELETE BackEnd_FeedbackCentral.*
FROM BackEnd_FeedbackCentral INNER JOIN [qryTopicBus in BEFC not in BID] ON (BackEnd_FeedbackCentral.[LRR Topic] = [qryTopicBus in BEFC not in BID].[LRR Topic]) AND (BackEnd_FeedbackCentral.[Bus Unit] = [qryTopicBus in BEFC not in BID].[Bus Unit]);

and got the same result

i'm going to try the IN statement instead

 

[Minty]

I think an In() will remove any ambiguity and should work.

I agree it's much easier / obvious in SQL Server....

 

[isladogs]

When the SQL for a delete query includes two or more tables (or queries based on them), it is often necessary to specify unique records.
i.e. DELETE DISTINCTROW .....

Did you try using a SELECT query first as suggested by both jdraw & myself?

 

[Isaac]

When the SQL for a delete query includes two or more tables (or queries based on them), it is often necessary to specify unique records.
i.e. DELETE DISTINCTROW .....

Did you try using a SELECT query first as suggested by both jdraw & myself?

Yes, I did. if I drag nothing down to the grid, Access (of course) will not run the query - must return at least one destination field.
Once I do draw something down (say, Backend_FeedbackCentral.*), it says please specify which table to delete.
The issue is not verifying the select - I'd already done so as mentioned in post 3.

Adding the distinctrow did not solve this particular problem

I ended up using the IN() and writing the SQL as follows and this was going to work:

DELETE * from BackEnd_FeedbackCentral where
BackEnd_FeedbackCentral.[Bus Unit] in(select [bus unit] from [qryTopicBus in BEFC not in BID])
and BackEnd_FeedbackCentral.[LRR Topic] in (select [lrr topic] from [qryTopicBus in BEFC not in bid])

but the warning about rows to be deleted was roughly twice as many as I wanted..which surprised me, isn't that the equivalent of my 2 inner joins??
I think I'm going to throw up my hands and write code and recordsets to do this one by one. I'm sure there's a better way but I'm clearly pretty rusty with Access queries.

 

[theDBguy]

but the warning about rows to be deleted was roughly twice as many as I wanted..which surprised me, isn't that the equivalent of my 2 inner joins??

Hi. I think this is the main (if not part of it) reason why you're getting the error message in the first place. The result of your query is like a combination of all the tables involved in the query.

However, let's say I have 10 records in one table I want to delete. But, when I join that table to another table, some of the data gets duplicated due to multiple matching records in the other table. As a result, Access might tell me I'm about to delete like 16 records, when I am expecting it to say only 10.

In that case, I go ahead and proceed because I know 16 is more than 10, so all the records I want gone will disappear after the process is done. Although Access will try to delete the same record more than once, it will actually only do it once.

Hope that makes sense...

 

[isladogs]

Adding the distinctrow did not solve this particular problem

but the warning about rows to be deleted was roughly twice as many as I wanted..which surprised me, isn't that the equivalent of my 2 inner joins??
I think I'm going to throw up my hands and write code and recordsets to do this one by one. I'm sure there's a better way but I'm clearly pretty rusty with Access queries.

My comment wasn't intended to solve the message asking which tables should be deleted...

It was general advice on dealing with DELETE queries where two or more tables are referenced.
In such cases, using a simple DELETE * may cause Access to prevent deletion as it isn't clear which records should be deleted.

However, adding DISTINCTROW should fix that and also solve the issue about the number of rows to be deleted