There is a Mysql database for wordpress.
It has a wp_signups table.
How can sql query remove all users from the wp_signups table, except for the first ten?

  • And what is the "first 10"? in general, something like delete from table where XXX> N, where N is possible 10, or maybe not, depending on the contents of the table - Mike
  • In SQL, there is no concept of "first N records"; the first records depend on sorting. - etki
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

First you need to determine how you define the first 10 entries? Since there is no default order in either the table of the relational model, nor in SQL standards based on the relational model, nor in MySql, it is necessary to explicitly specify the order.

See table

However, it’s not a rule.

Since the author of the question did not specify the structure of the table, we will assume that this is a table according to the following scheme: Database Description , contains the key user_login and the registered (datetime) field, according to which we take the first 10 records in ascending order of date. I don’t know for sure if user_login is a table key. We need to investigate this question separately, and establish what is actually the key before actually manipulating the table.

 DELETE FROM `wp_signups` WHERE `user_login` NOT IN ( SELECT `user_login` FROM `wp_signups` ORDER BY `registered` LIMIT 0, 10 );

The dialect allows you to make a request with a LIMIT clause without an ORDER BY clause, this is normal for requests that do not matter the composition of the response, for example - if we sequentially process records in batches, and erase after processing, but keep in mind how dangerous a similar request can be without ORDER BY, if order is really important. The order may differ from the version to the version of MySql, from the order of insertion into the table, from the current optimization plan, so the fact that once this query worked "correctly" does not mean that it will always work that way.

     DELETE FROM `wp_signups` WHERE `id` NOT IN (SELECT `id` FROM `wp_signups` ORDER BY `id` ASC LIMIT 0, 10);
    • 3
      typical shot in the leg. One b * gu knows that it will produce a SELECT id `FROM wp_signups LIMIT 0, 10` because the sorting flag is not set - Barmaley
    • @AntonioK and there exactly such quotes, but not 'wp_signups'? - Nick Volynkin ♦
    • @NickVolynkin infa 100%, so the names of the tables and fields are screened - AntonioK
    • @Barmaley, I can not remember now, not a single case, when select * from table (without explicitly specifying sorting!) Did not display the entries in chronological order ... Can you give an example? - Sergey Sereda
    • Let's first clarify what the chronological sequence is :) - Barmaley