How can I rewrite a DELETE query so I can limit the rows affected?

Question

I am aware that mysql does not allow use of LIMIT with multiple table delete queries. I have looked for a solution online but I am not sure how to adapt them to my need. Here is the query which deletes all content that is not in the content, to keep table along with other conditions.

DELETE f
    FROM   field_data_body f
           INNER JOIN node n
                   ON f.entity_id = n.nid
           LEFT JOIN content_to_keep k
                  ON n.nid = k.nid
    WHERE  n.type = 'article'
           AND k.nid IS NULL

So what options do I have to add a LIMIT param.

Edit: Thanks for the help, but most answers are using the where in clause. When I use it, I get an error saying "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME"subquery'

Probably something similar to this: http://stackoverflow.com/q/1062988. You need a subquery. — Robert Harvey, Jan 22 '13 at 17:00

score 2 · Answer 1 · answered Jan 22 '13 at 17:07

2

DELETE
FROM   field_data_body
WHERE id in (
    SELECT f.id
    FROM   field_data_body f
           INNER JOIN node n
                   ON f.entity_id = n.nid
           LEFT JOIN content_to_keep k
                  ON n.nid = k.nid
    WHERE  n.type = 'article'
           AND k.nid IS NULL)
LIMIT 10; --or whatever you want

answered Jan 22 '13 at 17:07

EkoostikMartin

6,831
2
33
62

I did this and I get This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – awm Jan 22 '13 at 18:13
@Ali: You are surely running some other version and not this query. This cannot give such error. – ypercubeᵀᴹ Jan 22 '13 at 21:39
Running it as is with replacing id with entity_id returns the follwoing error `You can't specify target table 'field_data_body' for update in FROM clause` Here is the query: `DELETE FROM field_data_body WHERE entity_id in ( SELECT f.entity_id FROM field_data_body f INNER JOIN node n ON f.entity_id = n.nid LEFT JOIN mbrd_delete_nodes_to_keep k ON n.nid = k.nid WHERE n.type = 'article' AND k.nid IS NULL) LIMIT 10;` – awm Jan 22 '13 at 22:58

score 2 · Accepted Answer · answered Jan 22 '13 at 17:13

2

Add ORDER BY and LIMIT in the part of the code that selects the rows to be deleted, then place it inside a derived table and join back to the table to be deleted:

DELETE f_del
FROM field_data_body AS f_del
   JOIN
      ( SELECT f.PK                            --- the Primary Key of the table
        FROM   field_data_body f
               INNER JOIN node n
                       ON f.entity_id = n.nid
               LEFT JOIN content_to_keep k
                      ON n.nid = k.nid
        WHERE  n.type = 'article'
               AND k.nid IS NULL
        ORDER BY some_column
        LIMIT 100
      ) AS tmp
      ON tmp.PK = f_del.PK ;

answered Jan 22 '13 at 17:13

ypercubeᵀᴹ

113,259
19
174
235

Also did this and I get This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – – awm Jan 22 '13 at 18:39
a MySQL-version which doesnt support LIMIT & IN?? This surely is an ancient version, you really should upgrade. – specializt Jan 22 '13 at 20:08
@Ali: Did you run this as it is? Without any change? What version of MySQL are you using? There is neither `IN` nor `ALL` nor `ANY` in this. – ypercubeᵀᴹ Jan 22 '13 at 20:40
@ypercube Sorry, I tried again and ran this `DELETE f_del FROM field_data_body AS f_del JOIN ( SELECT f.entity_id FROM field_data_body f INNER JOIN node n ON f.entity_id = n.nid LEFT JOIN mbrd_delete_nodes_to_keep k ON n.nid = k.nid WHERE n.type = 'forum' AND k.nid IS NULL ORDER BY n.created desc LIMIT 1 ) AS tmp ON tmp.entity_id = f_del.entity_id ; ` and it worked – awm Jan 22 '13 at 22:47
@specializt the version is `mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2` – awm Jan 22 '13 at 23:02
@ypercube By the way,is this a resource intensive query? From my bench-marking attemp, executing this on a relatively small database takes close to ~1 sec for `limit 1`. – awm Jan 22 '13 at 23:09
Try the `EXPLAIN` on the derived table (what is inside the parenthesis). It depends on the indexes you have. – ypercubeᵀᴹ Jan 22 '13 at 23:10

osoclever · Answer 3 · 2013-01-27T02:10:15.677

0

Maybe to get you in the right direction:

.... WHERE IN (SELECT ... FROM field_data_body WHERE nid IS NULL)

edited Jan 27 '13 at 02:10

answered Jan 22 '13 at 17:04

osoclever

373
7
16

This is tagged `MySQL`. There is no `TOP` in MySQL. – ypercubeᵀᴹ Jan 22 '13 at 20:42

score 0 · Answer 4 · edited May 23 '17 at 12:12

From another question I was able to do the following

DELETE f 
FROM   field_data_body f 
WHERE  f.entity_id IN (SELECT * 
                   FROM   (SELECT f.entity_id 
                           FROM   field_data_body f 
                                  INNER JOIN node n 
                                          ON f.entity_id = n.nid 
                                  LEFT JOIN mbrd_delete_nodes_to_keep k 
                                         ON n.nid = k.nid 
                           WHERE  n.type = 'forum' 
                                  AND k.nid IS NULL 
                                  AND f.entity_type = 'node' 
                           LIMIT  1) AS t)

How can I rewrite a DELETE query so I can limit the rows affected?

4 Answers4