CODEWITHSUNDEEP
mysqldelete-row
user756659
user756659

Reputation: 3512

mysql delete with inner joins and limit

This is the only way I could think of doing this and I am getting an error about the limit. I am trying to delete in chunks within the while loop as the result total might be quite large.

The first count statement works fine. It is the second, delete statement, which does not. I am guessing because of the joins and/or using limit. How can I limit the delete while still joining?

//find total count to delete
$stmt = $db->prepare("
    SELECT
        COUNT(*)
    FROM app_logs
    INNER JOIN users
        ON users.user_id = app_logs.user_id
    INNER JOIN computers
        ON computers.computer_id = users.computer_id AND computers.account_id != :account
    WHERE app_logs.timestamp < :cutoff_time
");

$binding = array(
    'account' => 2,
    'cutoff_time' => strtotime('-3 months')
);

$stmt->execute($binding);

//get total results count from above
$found_count = $stmt->fetch(PDO::FETCH_COLUMN, 0);

echo $found_count; //ex. 15324

//delete rows
$stmt = $db->prepare("
    DELETE
    FROM app_logs
    INNER JOIN users
        ON users.user_id = spc_app_logs.user_id
    INNER JOIN computers
        ON computers.computer_id = users.computer_id AND computers.account_id != :account       
    WHERE app_logs.timestamp < :cutoff_time
    LIMIT :limit
");

$binding = array(
    'account' => 2,
    'cutoff_time' => strtotime('-3 months'),
    'limit' => 2000
);

while($found_count > 0)
{
    $stmt->execute($binding);

    $found_count = $found_count - $binding['limit'];
}

Upvotes: 3

Views: 2822

Answers (1)

Kleskowy
Kleskowy

Reputation: 2668

As mentioned in the docs and also in this answer, LIMIT cannot be used along DELETE + JOIN.

If you need to somehow limit deleting, just create conditions for the DELETE statement, that will emulate your LIMIT section. For example you could follow these steps:

  1. Take minimum and maximum rows' ids from the query you want to delete values from (lets say: 10 and 200)
  2. Then choose bordering ids imitating your limit (so for limit 50, the bordering ids could be 50, 100, 150, 200)
  3. Then, for each bordering id, query a DELETE statement with WHERE having AND id <= ?, and put every bordering id in the place of ?.
  4. So you end up with 4 similar queries, each of them deleting ids from a certain range (10-50], (50, 100] etc..

Hope this helps.

Upvotes: 5

Related Questions