Solved

Delete query error

Posted on 2009-05-19
16
198 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:ba_trainer
  • 8
  • 6
  • 2
16 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24426579
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24430475
Make sure that entity id is in {Accounts on current call cycle but not on update]

Gary
0
 

Author Comment

by:ba_trainer
ID: 24431007
ralmada,

I am getting error: Select statement includes reserved word or an arguement name that is misspelled or the puncuation is incorrect.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24431731
>>arguement name that is misspelled or the puncuation is incorrect<<
check and double check the spelling. Both of table name and column names.
0
 

Author Comment

by:ba_trainer
ID: 24432253
I have checked everything...still same errors!
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24432277
Did you check that [entity id] is in [Accounts on current call cycle but not on update] ?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24432551
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
 

Author Comment

by:ba_trainer
ID: 24432901
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:ralmada
ID: 24434397
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
 

Author Comment

by:ba_trainer
ID: 24434917
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
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24435249
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
 

Author Comment

by:ba_trainer
ID: 24440415
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
 

Author Comment

by:ba_trainer
ID: 24440453
Wait, I could be mistaken...
0
 

Author Comment

by:ba_trainer
ID: 24440561
No, the Accounts on current call cycle but not on update QUERY is not correct now.
0
 

Author Closing Comment

by:ba_trainer
ID: 31583194
Thanks so much!! I finally worked it out!
Thanks for sticking with me! Very grateful!
Beth
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24441355
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now