MySQL: Updating rows with Select and Limit

Question

I am trying to UPDATE all rows on a table to the iterating values of a for loop based on a LIMIT, so that the update would be done in batches. I took a cue from a suggestion in the post update multiple rows using limit in mysql?, but it seems to be giving me a problem. The table has over 2400 rows, and the relevant field has all-0 values. Here's my code:

for($i = 0;$i < 7; $i++) {
    $qq2_ = $db1->prepare("UPDATE ledum_orgs SET ledum_org_size = :val WHERE ledum_org_size IN (
                            SELECT ledum_org_size FROM (
                                SELECT ledum_org_size FROM ledum_orgs WHERE ledum_org_size = '0'
                                ORDER BY ledum_org_name ASC
                                LIMIT ".($i*400).", 400
                            ) tmp
                          )"
                         );
    $qq2_->bindValue(':val',$i);
    $qq2_->execute();
    echo $i."=>".$qq2_->rowCount().", ";
}

But it seems to be disregarding the LIMIT clause, setting the field for all 2445 rows to '1'. Here's the output:

0=>0, 1=>2445, 2=>0, 3=>0, 4=>0, 5=>0, 6=>0,

Can someone tell me why this behaviour?

score 0 · Answer 1 · answered Dec 29 '14 at 15:20

In MySQL, you cannot use limit in a subquery used with in. Nor can you refer to the table being updated in a subquery (without using join). You can, however, switch this to a join:

UPDATE ledum_orgs lo JOIN
       (SELECT ledum_org_size
        FROM ledum_orgs
        WHERE ledum_org_size = '0'
        ORDER BY ledum_org_name ASC
        LIMIT ".($i*400).", 400
       ) tmp
       ON tmp.ledum_org_size = lo.ledum_org_size
    SET lo.ledum_org_size = :val;

It is possible that you can simplify this to:

UPDATE ledum_orgs lo
    SET lo.ledum_org_size = :val
    WHERE ledum_org_size = '0'
    ORDER BY ledum_org_name ASC
    LIMIT ".($i*400).", 400

MySQL: Updating rows with Select and Limit

1 Answers1