5

I have this problem while trying to select and delete. Can you guys help me to get this worked ?

SELECT `danyel_site`.`server_week`.* FROM `server_week` WHERE `weekly` = 0 AND `time` NOT IN (SELECT * FROM `server_week` WHERE `weekly` = 0 ORDER BY `time` DESC LIMIT 10)

I also want to use delete

DELETE FROM `server_week` WHERE `weekly` = 0 AND `time` NOT IN (SELECT * FROM `server_week` WHERE `weekly` = 0 ORDER BY `time` DESC LIMIT 10)
Dany
  • 119
  • 2
  • 5
  • 11

1 Answers1

19

you can do it with a subquery like that

DELETE FROM `server_week` WHERE `weekly` = 0 AND `time` NOT IN 
           (select `time` from ( 
                               SELECT `time` FROM `server_week` 
                                WHERE `weekly` = 0 
                                ORDER BY `time` DESC 
                                LIMIT 10) t )
echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • 2
    No I haven't, I'm just asking judging from the error message. Doesn't your query use `LIMIT` and `IN`, that _"This version of MySQL doesn't yet support"_? – geomagas Oct 13 '13 at 11:02
  • it will not say this error i said :) , its in subquery not like yours , yours is wrong mysql doent support directly limit. – echo_Me Oct 13 '13 at 11:11
  • 7
    It's not _my_ query. I'm not the OP... Just passing by and saw your answer, made me wonder and asked for an explanation, since _this_ never happened to me. I'd really like to see an explanation, but if you won't provide one, it's just as good, I'll just redirect my search elsewhere :) – geomagas Oct 13 '13 at 11:16
  • 1
    look this answer , http://stackoverflow.com/questions/7142097/mysql-delete-statement-with-limit – echo_Me Oct 13 '13 at 11:29
  • and this http://stackoverflow.com/questions/3271396/how-to-delete-duplicate-rows-from-a-table-in-mysql – echo_Me Oct 13 '13 at 11:30
  • This still doesn't work ; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 – Dany Oct 13 '13 at 19:03
  • It would not hurt to put some explanation as to why there is an error and why your answer would work. There are some people who are not just *after* the solution but also the reason behind it. :) – Gellie Ann Mar 08 '17 at 00:11
  • 2
    @GellieAnn it looks like in this case, you cant use IN directly with a subquery that utilizes a LIMIT. So, the workaround is to encapsulate them in yet another subquery to 'fool' the MySQL. – cipley Apr 12 '18 at 11:12
  • @cipley I see, so the error is because of `LIMIT`. Thanks. It can't update itself by referencing itself. Perhaps it needed the encapsulation to make it look like it is a different table. – Gellie Ann Apr 13 '18 at 05:39