Delete query error

I was using this query, now it is giving error
[entity id] could refer to more than one table in my FROM statement.

Can anyone help??
thanks!
Beth
DELETE *
FROM [District 3 total list1]
WHERE [District 3 total list1].[Entity ID] in (Select [entity id] from [Accounts on current call cycle but not on update]);

Open in new window

ba_trainerAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
I think the problem is that you have the [Entity ID] column in both [District 3] table and [Update Table].
Try changing your "Accounts on current call ... " query to explicitly define all columns except [Update table].[Entity ID]

SELECT [District 3 total list1].[Entity ID], ... all columns except [Update table].[Entity ID]...
FROM [District 3 total list1] LEFT JOIN [Update table] ON ([District 3 total list1].[Entity ID] = [Update table].[Entity ID]) AND ([District 3 total list1].[Facility ID] = [Update table].[Facility ID])
WHERE ((([Update table].[Entity ID]) Is Null));

Open in new window

0
 
ralmadaCommented:
What about this?
DELETE 
FROM [District 3 total list1]
WHERE [District 3 total list1].[Entity ID] in (Select [Accounts on current call cycle but not on update].[entity id] from [Accounts on current call cycle but not on update]);

Open in new window

0
 
tbsgadiCommented:
Make sure that entity id is in {Accounts on current call cycle but not on update]

Gary
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ba_trainerAuthor Commented:
ralmada,

I am getting error: Select statement includes reserved word or an arguement name that is misspelled or the puncuation is incorrect.
0
 
ralmadaCommented:
>>arguement name that is misspelled or the puncuation is incorrect<<
check and double check the spelling. Both of table name and column names.
0
 
ba_trainerAuthor Commented:
I have checked everything...still same errors!
0
 
tbsgadiCommented:
Did you check that [entity id] is in [Accounts on current call cycle but not on update] ?
0
 
ralmadaCommented:
What about this?

DELETE *
FROM [District 3 total list1]
WHERE exists(select 1 from [Accounts on current call cycle but not on update] where [Accounts on current call cycle but not on update].[Entity ID] = [District 3 total list1].[Entity ID]) = true

Open in new window

0
 
ba_trainerAuthor Commented:
Error: The specified field [Accounts on current call cycle but not on update].[Entity ID] could refer to more than one table listed in the FROM clause of your SQL statement.
0
 
ralmadaCommented:
And this?
DELETE [District 3 total list1].*
FROM [District 3 total list1]
WHERE exists(select 1 from [Accounts on current call cycle but not on update] where [Accounts on current call cycle but not on update].[Entity ID] = [District 3 total list1].[Entity ID]) = true

Open in new window

0
 
ba_trainerAuthor Commented:
Same error.

tbsgadi:Did you check that [entity id] is in [Accounts on current call cycle but not on update] ?
Yes, I did - they will always be on the list and maybe this is the problem.

The "Accounts on current call cycle but not on update" is a QUERY based on the TABLE "District 3 total list1". It compares two tables and tells me the Entities that should be deleted. Then I was writing this query to take that list and delete them. Do you think this could be why?

This is the "Accounts on current call cycle but not on update"QUERY:

SELECT *
FROM [District 3 total list1] LEFT JOIN [Update table] ON ([District 3 total list1].[Entity ID] = [Update table].[Entity ID]) AND ([District 3 total list1].[Facility ID] = [Update table].[Facility ID])
WHERE ((([Update table].[Entity ID]) Is Null));
0
 
ba_trainerAuthor Commented:
I end up with the following, but it excludes everything. There are no longer 3 accounts correctly identified as existing on the [District 3] table that are not on the [Update table].

If I ran the delete, it would delete everything on the District 3] table.
SELECT [District 3 total list1].[_ID], [District 3 total list1].[Emp Notes], [District 3 total list1].[Entity ID], [District 3 total list1].[Facility ID], [District 3 total list1].FXSAcct, [District 3 total list1].[CC Band], [District 3 total list1].FXSName, [District 3 total list1].QOQ1, [District 3 total list1].QOQ2, [District 3 total list1].LASTCALLED, [District 3 total list1].LASTSCHEDULED, [District 3 total list1].NEXTCALL, [District 3 total list1].SOURCE, [District 3 total list1].Classify, [District 3 total list1].[Total Spend], [District 3 total list1].Zip, [District 3 total list1].[Hrs Access], [District 3 total list1].[Content Type], [District 3 total list1].[File Type], [District 3 total list1].Attachments, [District 3 total list1].[Workflow Instance ID], [District 3 total list1].Modified, [District 3 total list1].Created, [District 3 total list1].[Created By], [District 3 total list1].[Modified By], [District 3 total list1].[URL Path], [District 3 total list1].Path, [District 3 total list1].[Item Type], [District 3 total list1].[Encoded Absolute URL]
FROM [District 3 total list1] LEFT JOIN [Update table] ON ([District 3 total list1].[Facility ID] = [Update table].[Facility ID]) AND ([District 3 total list1].[Entity ID] = [Update table].[Entity ID])
WHERE ((([Update table].[Entity ID]) Is Null));

Open in new window

0
 
ba_trainerAuthor Commented:
Wait, I could be mistaken...
0
 
ba_trainerAuthor Commented:
No, the Accounts on current call cycle but not on update QUERY is not correct now.
0
 
ba_trainerAuthor Commented:
Thanks so much!! I finally worked it out!
Thanks for sticking with me! Very grateful!
Beth
0
 
ralmadaCommented:
That's weird. What if you create a delete query  directly (without using [Accounts on current call ...])?

DELETE [District 3 total list1].*
FROM [District 3 total list1]
LEFT JOIN [Update table] ON ([District 3 total list1].[Facility ID] = [Update table].[Facility ID]) AND ([District 3 total list1].[Entity ID] = [Update table].[Entity ID])
WHERE [Update table].[Entity ID] Is Null;

Open in new window

0
All Courses

From novice to tech pro — start learning today.