更新已删除行下的行

Question

If I run a multiple query like this that deletes the rows from the tables answer_det and question where I specify the pID and qID:

$deldil = "DELETE FROM answer_det WHERE pid='" . $_SESSION['pid'] . "' AND qid ='$qid';";
$deldil .= "DELETE FROM question WHERE pid='" . $_SESSION['pid'] . "' AND qid ='$qid';";
    $rundeldilemma = mysqli_multi_query($mysqli,$deldil);  

It will look like this for example in the question table if I delete the row where pID=1 and qID=2:

How would I do if I at the same time would like to update all the rows below the deleted one so the second row with qID=3 gets qID=2, the one with qID=4 gets qID=3 and so on. I just want to do it on the rows below the deleted one but only on those with the same pID that I have specified. That should happen on both of the tables, 'answer_det' and 'question'.

Answer 1

You can update the rows using the query below. What this does it searches for all the qid's bigger than the deleted row's qid and decreases it by 1.

"UPDATE `question` SET `qid` = `qid` - 1 WHERE `pid` = '".$_SESSION['pid']."' AND `qid` > '$qid'"