Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete query error

Posted on 2009-05-19
16
Medium Priority
?
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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
 
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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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