Deleting rows using a limit and offset without using IN clause-CodePudding

I want to delete rows with an offset, so I am forced to use a nested query since its not support in the raw DELETE clause.

I know this would worked (ID is the primary key):

DELETE FROM customers
WHERE ID IN (
    SELECT ID
    FROM customers
    WHERE register_date > '2012-01-01'
    ORDER BY register_date ASC
    LIMIT 5, 10
);

However, this is unsupported in my version as I get the error

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Server version: 10.4.22-MariaDB

What can I do to achieve the same result as above that is supported in my version.

CREATE TABLE customers (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
REGISTER_DATE DATETIME NOT NULL
);

CodePudding user response：

Join the table to a subquery that uses ROW_NUMBER() window function to sort the rows and filter the rows that you want to be deleted:

DELETE c
FROM customers c
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY register_date) rn
  FROM customers
  WHERE register_date > '2012-01-01'
) t ON t.ID = c.ID
WHERE t.rn > 5 AND t.rn <= 15; -- get 10 rows with row numbers 6 - 15

See the demo.

CodePudding user response：

You could try assigning a rank to your rows with the ROW_NUMBER window function, then catch those rows whose rank position is between 5 and 15.

DELETE FROM customers
WHERE ID IN (
    SELECT * 
    FROM (SELECT ID, 
                 ROW_NUMBER() OVER(
                     ORDER BY IF(register_date>'2012-01-01', 0, 1)
                              register_date                       ) AS rn
          FROM customers) ranked_ids
    WHERE rn > 4 
      AND rn < 16
);

This would safely avoid the use of LIMIT, though achieves the same result.

EDIT. Doing it with a join.

DELETE FROM customers c
INNER JOIN (SELECT ID, 
                   ROW_NUMBER() OVER(
                        ORDER BY IF(register_date>'2012-01-01', 0, 1)
                                    register_date                       ) AS rn
                  FROM customers) ranked_ids
            WHERE 
           ) ids_to_delete
        ON c.ID = ids_to_delete.ID
       AND ids_to_delete.rn > 4 
       AND ids_to_delete.rn < 16

CodePudding user response：

If I did not miss something a simple delete with join will do the job...

delete customers
from (select *
      from customers
      WHERE register_date > '2012-01-01'
      order by register_date asc
      limit 5, 2) customers2 
join customers on customers.id = customers2.id

Here is a demo for your version of MariaDB