Is this delete query right? (1 Viewer)

Isaac

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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.

Code:
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

Super Moderator
Staff member
Local time
Yesterday, 22:22
Joined
Jan 23, 2006
Messages
13,629
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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

I’m here to help
Staff member
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,560
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

Super Moderator
Staff member
Local time
Yesterday, 22:22
Joined
Jan 23, 2006
Messages
13,629
It should only delete from the one table-- unless there are related records in another table and Cascade Delete is set.
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
Right-on, ok gentlemen, thanks a lot! Have a wonderful rest of your Thursday.
 

isladogs

CID VIP
Local time
Today, 03:22
Joined
Jan 14, 2017
Messages
14,697
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
OK, access is now telling me as you predicted, specify the table you want to delete.

But how? Seem like I already have?

1605809256466.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,560
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

AWF VIP
Local time
Today, 03:22
Joined
Jul 26, 2013
Messages
8,173
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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?
Code:
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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

I’m here to help
Staff member
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,560
Code:
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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

AWF VIP
Local time
Today, 03:22
Joined
Jul 26, 2013
Messages
8,173
I think an In() will remove any ambiguity and should work.

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

isladogs

CID VIP
Local time
Today, 03:22
Joined
Jan 14, 2017
Messages
14,697
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

Lifelong Learner
Local time
Yesterday, 19:22
Joined
Mar 14, 2017
Messages
4,339
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

1605817970674.png


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

Code:
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

I’m here to help
Staff member
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,560
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

CID VIP
Local time
Today, 03:22
Joined
Jan 14, 2017
Messages
14,697
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
 

Users who are viewing this thread

Top Bottom