php - 如何在 SQL 中执行多个更新查询

Question

因此，当我删除表中的条目时，我想更新每一列 oneToFive (这是从 1 到 5 的数字):

所以我的 table 看起来像这样

|oneToFive|name|
----------------
|1        |a   |
|2        |b   |
|3        |c   |
|4        |d   |
|5        |e   |

假设我删除名为 d 的行，表格将如下所示:

|oneToFive|name|
----------------
|1        |a   |
|2        |b   |
|3        |c   |
|5        |e   |

我希望它看起来像:

|oneToFive|name|
----------------
|1        |a   |
|2        |b   |
|3        |c   |
|4        |e   |

表格也可能不满，例如它可能是这样的:

|oneToFive|name|
----------------
|1        |a   |
|2        |b   |
|3        |c   |

如果我删除了一个，我会想要它:

|oneToFive|name|
----------------
|1        |b   |
|2        |c   |

所以我尝试了这个疯狂的代码，我编写了所有更新查询，然后执行删除行后存在的查询。但这不起作用。

//find query
if($result === 5){

}else if($result === 4){
    $query2 = "UPDATE faveMonth
    SET oneToFive='4'
    WHERE oneToFive='5'";
}else if($result === 3){
    $query3 = "UPDATE faveMonth
    SET oneToFive='3'
    WHERE oneToFive='4'";
    $query2 = "UPDATE faveMonth
    SET oneToFive='4'
    WHERE oneToFive='5'";
}else if($result === 2){
    $query4 ="UPDATE faveMonth
    SET oneToFive='2'
    WHERE oneToFive='3'";
    $query3 = "UPDATE faveMonth
    SET oneToFive='3'
    WHERE oneToFive='4'";
    $query2 = "UPDATE faveMonth
    SET oneToFive='4'
    WHERE oneToFive='5'";
}else {
    $query5 ="UPDATE faveMonth
    SET oneToFive='1'
    WHERE oneToFive='2'";
    $query4 ="UPDATE faveMonth
    SET oneToFive='2'
    WHERE oneToFive='3'";
    $query3 = "UPDATE faveMonth
    SET oneToFive='3'
    WHERE oneToFive='4'";
    $query2 = "UPDATE faveMonth
    SET oneToFive='4'
    WHERE oneToFive='5'";
}

//run querys
if($result === 5){

}else if($result === 4){
    if(mysqli_query ($con,$query2))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
}else if($result === 3){
    if(mysqli_query ($con,$query2))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }

    if(mysqli_query ($con,$query3))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
}else if($result === 2){
    if(mysqli_query ($con,$query2))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }

    if(mysqli_query ($con,$query3))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
    if(mysqli_query ($con,$query4))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
}else {
    if(mysqli_query ($con,$query2))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }

    if(mysqli_query ($con,$query3))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
    if(mysqli_query ($con,$query4))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
    if(mysqli_query ($con,$query5))
        {

        }
    else
        {
            die("error updating other row(s)");  
        }
}

你能想到更好的方法吗？

Answer 1

您可以使用这样的单个语句

UPDATE faveMonth SET oneToFive = oneToFive - 1 WHERE oneToFive > x

一旦您使用 oneToFive == x 删除条目