CODEWITHSUNDEEP
mysql
Kevin
Kevin

Reputation: 86

How can I limit this DELETE query?

I've got this query im executing, I want to put this in a while loop in a PHP script. Therefor I need to limit the rows that will be deleted to 50 000. Can anyone explain to me how I do this?

My query is:

DELETE a
FROM activities a INNER JOIN backup_activities b
ON a.id = b.id
LIMIT 50000 ??????????

Thanks!

Upvotes: 0

Views: 76

Answers (3)

DRapp
DRapp

Reputation: 48139

You would probably have to do a correlated delete. Basically like AZIZ had, however, that will only work partially because it may not encompass the IDs in activities, or return IDs already deleted. You need to make sure you are getting IDs that are STILL available.

Here this should help, and obviously change the 5 to whatever limit count you want. As you can see too, the subquery is only getting IDs that are BOTH in activities AND backup_activities tables.

DELETE a1 
   FROM activities a1 
      JOIN ( select a.id
                from activities a
                JOIN backup_activities b 
                where a.id = b.id
                limit 5 ) killThese
         on a1.id = killThese.id

Feedback, consideration.

Although the other solution IS working, and we don't know how your data is populated in the respective tables, consider the following scenario...

Table Activities has records for ID 1-100 Table Backup_Activities has records for 1-100. Now, consider the query is limited to 20 records.

The join to the 20 records returned from backup_activities is only IDs 1-20. So, first pass deletes records in activity table for IDs 1-20.

Now, second pass. of trying to delete 20 records. The backup_activities table still returns the same 20 records 1-20 IDs, so NOTHING is going to get deleted.

Now, consider again my solution. Since my query is JOINED to the activities, first pass will return IDs 1-20 and delete from activities table ID 1-20. Now, SECOND pass on my query... since a JOIN by the IDs, it is returning IDs 21-40 and deletes again from activities table.

Again, don't know how your table is handled, but just consideration on what your query will be doing.

Upvotes: 2

Jason Heo
Jason Heo

Reputation: 10246

What about this?

DELETE a
FROM activities a
WHERE EXISTS (
    SELECT 1
    FROM backup_activities b
    WHERE a.id = b.id
)
LIMIT 50000

BTW

"I don't think a limit on a delete is valid.. It won't execute"

MySQL supports LIMIT on DELETE. Refer to http://dev.mysql.com/doc/refman/5.5/en/delete.html

Upvotes: 2

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

DELETE a
FROM activities AS a
JOIN
  ( SELECT b.id
    FROM   backup_activities b
    ORDER BY b.id
    LIMIT 50000
  ) AS tmp
  ON tmp.id = a.id

Upvotes: 2

Related Questions