Solved

Delete query error

Posted on 2009-05-19
16
201 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 24 52
Help with Oracle IF statment 5 35
Duplicated data in GROUP_CONCAT 2 15
Update the value of table B with value of table A tables inner join 6 75
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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