CODEWITHSUNDEEP
mysql
markzz
markzz

Reputation: 1262

MySQL: Delete rows that have a value in another select

I'm trying to delete rows from a table based on what's not in one of the keys (previous programmer didn't make sure that values aren't removed if a key is missing, yada yada) and this is the statment I tried:

DELETE FROM studentinstruments 
WHERE studentID IN 
(SELECT DISTINCT studentID 
FROM studentinstruments 
WHERE studentID NOT IN 
(SELECT ID FROM students));

And I get an error of "ERROR 1093 (HY000): You can't specify target table 'studentinstruments' for update in FROM clause" and I have no idea what's wrong with it. What would the corrections be to make this statement work?

Upvotes: 0

Views: 207

Answers (2)

Ritt
Ritt

Reputation: 3349

The problem is in Mysql, you can't make modification in the same table, which you have used in the select part.

Instead try using the instance.

DELETE FROM studentinstruments
WHERE studentID IN
(
    SELECT studentId
    FROM (SELECT * FROM studentinstruments) AS something
    WHERE something.studentID NOT IN (SELECT * FROM studentinstruments)
)

Upvotes: 0

Techie
Techie

Reputation: 1491

Why not use it directly instead of using multiple subquery, it would do the same thing.

DELETE FROM studentinstruments 
WHERE studentID NOT IN 
(SELECT ID FROM students);

Upvotes: 1

Related Questions