mySQL为多个更新的行添加1到一个数字

Question

Hey all i have the following update for my table in mySQL:

$dbBuilder = '';

foreach($_POST as $key => $value)
{
    if ($value != 'APPROVE') {
        $dbBuilder = $value . ", " . $dbBuilder;
    }
}

$dbBuilder = '(' . rtrim($dbBuilder, ", ") . ')';

UPDATE userCase SET Accepted = 1 WHERE CaseNum in $dbBuilder

Works fine as it should... but now i needed to modify it to allow a new QNum for each of whatever CaseNum.

$result = mysql_query("SELECT QNum FROM userCase ORDER BY QNum DESC limit 1");

while($row = mysql_fetch_assoc($result)) {
    $QNum = $row["QNum"] + 1;
    break;
}

UPDATE userCase SET Accepted = 1, QNum = " . $QNum . " WHERE CaseNum in $dbBuilder

Doing it that way above would set QNum to the same number for all CaseNum in "in". It needs to be unique (as in, adding a 1 to the QNum for each new row).

It only needs to be 1 more than the last QNum in the table. So when someone "approves" a few things (lets say 3 things), it goes to the query above and it needs to be (lets say the last number is 4 in the DB) so it needs to be 5,6,7. The code i have now would just do 5,5,5.

How would i accomplish this using the same CaseNum in.... code above?

Thanks!

Answer 1

UPDATE userCase as u1 set Accepted = 1,
    QNum = (select 
        count(*) 
        from userCase as u2 
        where QNum is not null 
        or (
            u2.CaseNum in (...)
            and u2.CaseNum < u1.CaseNum
        ))
where CaseNum in (...)