Solved

Need help with DELETE SQL Query that has a SELECT in WHERE Clause

Posted on 2009-06-29
14
246 Views
Last Modified: 2012-05-07
What i want to do is Delete the records from table tbl_FS_Category where the InternalID is equal to the select query results of a statement that includes the table i wish to delete from.

Not sure if this is possible or not, but if not i think i can get the same result by building a temp table with the results and then running a query  on that table instead.

Any thoughts would be greatly appreciated!
DELETE FROM tbl_FS_Category WHERE tbl_FS_Category.InternalID = 
 
SELECT tbl_FS_Category.InternalID
FROM tbl_FS_Category
LEFT OUTER JOIN tbl_FS_Items_Basic ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
WHERE tbl_FS_Items_Basic.InternalID IS NULL

Open in new window

0
Comment
Question by:Fullsource
[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
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24737628
DELETE FROM tbl_FS_Category WHERE tbl_FS_Category.InternalID  IN (
 
SELECT tbl_FS_Category.InternalID
FROM tbl_FS_Category
LEFT OUTER JOIN tbl_FS_Items_Basic ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
WHERE tbl_FS_Items_Basic.InternalID IS NULL

)
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24737629
Try something like this.
DELETE 
FROM tbl_FS_Category 
WHERE tbl_FS_Category.InternalID IN ( 
                                      SELECT tbl_FS_Category.InternalID
                                      FROM   tbl_FS_Category LEFT OUTER JOIN tbl_FS_Items_Basic 
                                             ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
                                      WHERE tbl_FS_Items_Basic.InternalID IS NULL
                                     )

Open in new window

0
 

Author Comment

by:Fullsource
ID: 24737708
I get this error when trying your statements...


#1093 - You can't specify target table 'tbl_FS_Category' for update in FROM clause
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Fullsource
ID: 24737722
That is why i think i have to create a temp table...but again, if i can do this all in one statement that would be freakin awesome.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24737763
DELETE tbl_FS_Category WHERE tbl_FS_Category.InternalID  IN (
 
SELECT tbl_FS_Category.InternalID
FROM tbl_FS_Category
LEFT OUTER JOIN tbl_FS_Items_Basic ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
WHERE tbl_FS_Items_Basic.InternalID IS NULL

)
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24737775
Hmm.. in that case create a temporary table and put all values in the temp table and then do a select statement from the temp table instead of the subquery:
CREATE TEMPORARY TABLE Temp ( ID  INT)
INSERT INTO Temp
SELECT tbl_FS_Category.InternalID
                                      FROM   tbl_FS_Category LEFT OUTER JOIN tbl_FS_Items_Basic
                                             ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
                                      WHERE tbl_FS_Items_Basic.InternalID IS NULL
 
DELETE FROM tbl_FS_Category WHERE tbl_FS_Category.InternalID IN ( Select ID From Temp)


0
 
LVL 32

Expert Comment

by:awking00
ID: 24737974
See attached.
comments.txt
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24738026
You can do this in one statement, you will need an INNER JOIN.
-- syntax to inner join to a list of inclusive values to delete
DELETE fsc1
FROM tbl_FS_Category fsc1
INNER JOIN (
SELECT tbl_FS_Category.InternalID
FROM tbl_FS_Category
LEFT OUTER JOIN tbl_FS_Items_Basic ON tbl_FS_Items_Basic.InternalID = tbl_FS_Category.InternalID
WHERE tbl_FS_Items_Basic.InternalID IS NULL
) fsc2 ON fsc1.InternalID = fsc2.InternalID;
 
-- shorter version as your left join can just be put in the delete
DELETE fsc
FROM tbl_FS_Category fsc
LEFT OUTER JOIN tbl_FS_Items_Basic fsib ON fsib.InternalID = fsc.InternalID
WHERE fsib.InternalID IS NULL;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24738057
awking00, with MySQL you get the error:
MySQL said: Documentation
#1093 - You can't specify target table 'your_table_name' for update in FROM clause

Have to use join approach, but I agree with you that it is simply a delete from one table where doesn't exist in another table, so my second example above is equivalent to yours.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24738244
See attached.
comments.txt
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24738315
awking00, that is the error that you get.  NOT IN is another good method for this case.  The point is a temporary table is not necessary.  

Why are you commenting using file attachments, BTW?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24738363
The join approach has to be used where you would typically use EXISTS because of the error noted when you have multiple fields to compare.  When just one that IN or NOT IN can work that should be fine.  Also if the subquery is to the same table as originally being used in this question, even the (NOT) IN solution will yield the same error message.

Anyway, hopefully, Fullsource is still monitoring to benefit from this additional information. :)
0
 
LVL 32

Expert Comment

by:awking00
ID: 24738403
See attached.
comments.txt
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24738477
LOL. :)
EXISTS works in MySQL, just not with the DELETE syntax.  Actually try the queries in MySQL some time and you will see what I mean.
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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