CODEWITHSUNDEEP
mysql
awm
awm

Reputation: 1200

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

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'

Upvotes: 2

Views: 243

Answers (4)

osoclever
osoclever

Reputation: 373

Maybe to get you in the right direction:

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

Upvotes: 0

awm
awm

Reputation: 1200

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)

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115560

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 ;

Upvotes: 2

EkoostikMartin
EkoostikMartin

Reputation: 6911

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

Upvotes: 2

Related Questions