0

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?

yash
  • 2,101
  • 2
  • 23
  • 32

1 Answers1

1

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
   );
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • i'm using single table only `job detail history`. and i want to keep 2 records based on start_time desc. – yash Aug 14 '18 at 09:45
  • you have not modified limit or IN used in query. but still it works. How? because error code 1235 display "This version of MySQL doesn't yet support 'LIMIT & IN" – yash Aug 15 '18 at 10:20
  • @yash support but in different way like experts suggest just use a layer upon it then use limit and in it will works – Zaynul Abadin Tuhin Aug 15 '18 at 10:27