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

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

FullsourceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
pssandhuCommented:
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
FullsourceAuthor Commented:
I get this error when trying your statements...


#1093 - You can't specify target table 'tbl_FS_Category' for update in FROM clause
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

FullsourceAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
pssandhuCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
See attached.
comments.txt
0
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
awking00Information Technology SpecialistCommented:
See attached.
comments.txt
0
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
awking00Information Technology SpecialistCommented:
See attached.
comments.txt
0
Kevin CrossChief Technology OfficerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.