CODEWITHSUNDEEP
mysqlmysql-workbench
yash
yash

Reputation: 2271

MYSQL DELETE with SUBQUERY gives Error code:1235

I have one table Job_Detail_History, which contains following structure and data:

enter image description here

When i try to delete query using subquery with LIMIT, NOT IN, 

DELETE FROM job_detail_history where id not in(select id from job_detail_history order by start_time desc limit 2);


it's giving me :

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Then i try to find some solution over SO:
i found this one as global solution, which tells to use JOIN instead of NOT IN, but in that case they are using select query. So, the query looks like this :

SELECT * from job_detail_history INNER JOIN (SELECT ID FROM job_detail_history order by start_time desc limit 2) as v2 limit 2;

and it will result a new table as result like this :

enter image description here

So, my question is how to handle DELETE scenario in this case?

Upvotes: 0

Views: 166

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

by using join you can delete as well below is an example

DELETE t1,t2 FROM t1
        INNER JOIN
    t2 ON t2.ref = t1.id 
WHERE
    t1.id = 1;

For your case change a bit

SET SQL_SAFE_UPDATES = 0;  

    DELETE FROM job_detail_history where id not in
    ( select * from 
               (select id from 
                job_detail_history order by start_time desc limit 2)  
                as t1
   );

Upvotes: 1

Related Questions