0

if I want to delete some data in mysql, can I use

delete from table where xxx='xxx' and indexID not in (select * table where xxx='xxx limit 3)??

because when I used this code, I got the error.

I know how to working in mssql,

DELETE FROM table WHERE xxx = 'xxx' AND indexID NOT IN (SELECT TOP(3) indexID FROM table WHERE xxx = 'xxx' ORDER BY check_date DESC

Andy
  • 1
  • 2
  • In your nested query it shouldn't be select * but it should be selecting the id column only – Wadih M. Aug 09 '21 at 01:38
  • 2
    `I got the error` ... what was the error? Also note that using `LIMIT` without `ORDER BY` generally makes no sense. You should explain the logic of your query, maybe by adding sample data. – Tim Biegeleisen Aug 09 '21 at 01:44
  • @WadihM. I'd tried used SELECT indexID FROM sis_User_password_check WHERE userid ='admin' order by chk_date, but will got This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Andy Aug 09 '21 at 01:54
  • @TimBiegeleisen Because I have to delete the last three data – Andy Aug 09 '21 at 01:55
  • After googling your error message, I find this post, which might have your solution. Essentially it is using a join instead of a IN subquery. https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu. Make sure to do some testing to get the algorithm right – Wadih M. Aug 09 '21 at 02:03
  • @dMd So I cannot do it in mysql? but can do it in mssql? – Andy Aug 09 '21 at 02:03
  • You can use joins or double subquery if you deleting and selecting from same table: https://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition –  Aug 09 '21 at 03:36

0 Answers0