当满足条件时退出SQL-EXIT CASE语句

Question

当满足条件时退出SQL-EXIT CASE语句

4

以下的CASE语句在ID=10时同时更新BET1、BET2、BET3和BET4。

$S1=32
$sql="UPDATE TIMER-BOARD1 
set BET1= case when BET1 IS NULL THEN $S1 else BET1 end ,
    BET2= case when BET2 IS NULL THEN $S1 else BET2 end,
    BET3= case when BET3 IS NULL THEN $S1  else BET3 end,
    BET4= case when BET4 IS NULL THEN $S1 else BET4 end
WHERE ID = 10";

当我运行php页面时，我有BET1 = 32，BET2 = 32，BET3 = 32，BET4 = 32。

我想要的是当脚本找到并填充第一个null字段时退出。

也就是说，从所有BET1，BET2，BET3，BET4 = NULL when ID =10开始，当我运行php页面时，只更新BET1 = 32。再次运行它时，更新BET2 = 32，等等。

类似于以下内容：

    $S1=32
    $sql="UPDATE TIMER-BOARD1 
    set BET1= case when BET1 IS NULL THEN $S1 
( EXIT AND DON’T BOTHER ABOUT OTHER CASE      STATEMENTS) else BET1 end ,
       BET2= case when BET2 IS NULL THEN $S1 
(EXIT AND DON’T BOTHER ABOUT OTHER CASE STATEMENTS) else BET2 end,
    BET3= case when BET3 IS NULL THEN $S1 
(EXIT AND DON’T BOTHER ABOUT OTHER CASE STATEMENTS)   else BET3 end,
    BET4= case when BET4 IS NULL THEN $S1 
(EXIT AND DON’T BOTHER ABOUT OTHER CASE STATEMENTS) else BET4 end
    WHERE ID = 10";

结果

Start values: ID=10 ,BET1=NULL, BET2=NULL, BET3=NULL, BET4=NULL.

Run the script 1ST time: ID=10, BET1=32, BET2 =NULL, BET3=NULL, BET4=NULL.

Run the script 2nd time: ID=10, BET1=32, BET2= 32, BET3=NULL, BET4 = NULL.

Run the script 3rd time: ID=10, BET1=32, BET2=32, BET3=32, BET4=NULL.

Run the script 4th time: ID=10, BET1=32, BET2=32, BET3=32, BET4=32.

Run the script 5th time or more: No null field, so no change.

如何编辑脚本以执行上述功能？

编辑

@ InoS Heo

如果您有时间，还有一个最后的问题。

在运行SQL之前，我的$S1=32。假设我有一个字符串susan而不是数字32。

如何更新您的脚本以适应以上类似的字符串？

$S1 =susan
UPDATE `TIMER-BOARD1`
set 
    ID = @UPDATED := 10,
    BET1 = case when BET1 IS NULL THEN @UPDATED:= susan else BET1 end,
    BET2 = case when @UPDATED = 10 AND BET2 IS NULL THEN @UPDATED:= susan else BET2     end,
…
    BET4 = case when @UPDATED = 10 AND BET4 IS NULL THEN @UPDATED:= susan else BET4 end
WHERE ID = 10;

谢谢。

- user3106907

2个回答

0

你可以简单地检查其他字段是否已经不是null值：

UPDATE TIMER-BOARD1 
SET BET1= case when BET1 IS NULL THEN $S1 else BET1 end ,
    BET2= case when BET1 IS NOT NULL AND BET2 IS NULL THEN $S1 else BET2 end,
    BET3= case when BET1 IS NOT NULL AND BET2 IS NOT NULL AND BET3 IS NULL THEN $S1 else BET3 end,
    BET4= case when BET1 IS NOT NULL AND BET2 IS NOT NULL AND BET3 IS NOT NULL AND BET4 IS NULL THEN $S1 else BET4 end
WHERE ID = 10

- Mureinik

感谢您的帮助。您的方法像以前一样更新了所有字段，没有任何变化。 - user3106907

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jason Heo · Accepted Answer

你正在寻找“更新第一个NULL列”的方法。我认为可以通过使用MySQL用户变量来实现。

你可以尝试一下这个方法：

架构和数据

DROP TABLE IF EXISTS `TIMER-BOARD1`;
CREATE TABLE `TIMER-BOARD1` (
  `ID` int(11) NOT NULL,
  `BET1` int(11) DEFAULT NULL,
  `BET2` int(11) DEFAULT NULL,
  `BET3` int(11) DEFAULT NULL,
  `BET4` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM `TIMER-BOARD1`;
INSERT INTO `TIMER-BOARD1` VALUES (10, NULL, NULL, NULL, NULL),
    (10, 32, NULL, NULL, NULL), (10, 1, 2, NULL, NULL),
    (10, 1, 2, 3, NULL), (10, 1, 2, 3, 4);

初始数据

SELECT * FROM `TIMER-BOARD1`;
+----+------+------+------+------+
| ID | BET1 | BET2 | BET3 | BET4 |
+----+------+------+------+------+
| 10 | NULL | NULL | NULL | NULL |
| 10 |   32 | NULL | NULL | NULL |
| 10 |    1 |    2 | NULL | NULL |
| 10 |    1 |    2 |    3 | NULL |
| 10 |    1 |    2 |    3 |    4 |
+----+------+------+------+------+

更新

UPDATE `TIMER-BOARD1`
set 
    ID = @UPDATED := 10, -- user defined variable which keep track WHETHERE CHANGED OR NOT
    BET1 = case when BET1 IS NULL THEN @UPDATED:= 32 else BET1 end,
    BET2 = case when @UPDATED = 10 AND BET2 IS NULL THEN @UPDATED:=32 else BET2 end,
    BET3 = case when @UPDATED = 10 AND BET3 IS NULL THEN @UPDATED:=32 else BET3 end,
    BET4 = case when @UPDATED = 10 AND BET4 IS NULL THEN @UPDATED:=32 else BET4 end
WHERE ID = 10;

更新的数据。

SELECT * FROM `TIMER-BOARD1`;
mysql> SELECT * FROM `TIMER-BOARD1`;
+----+------+------+------+------+
| ID | BET1 | BET2 | BET3 | BET4 |
+----+------+------+------+------+
| 10 |   32 | NULL | NULL | NULL |
| 10 |   32 |   32 | NULL | NULL |
| 10 |    1 |    2 |   32 | NULL |
| 10 |    1 |    2 |    3 |   32 |
| 10 |    1 |    2 |    3 |    4 |
+----+------+------+------+------+

问题

在更新第10个时仍然存在问题。

已更新

1. 使用一些字符串列

我认为你有两个选择

使用字符串列而不是ID列
- 优点：可以使用一个UPDATE实现
- 缺点：如果没有更多的字符串列，则无法使用
对每一行执行UPDATE
- 优点：即使没有更多的字符串列，也可以使用
- 缺点：需要执行多个UPDATE，如果没有主键，则无法使用。

我想知道是否有像string_col这样的一些字符串列。

模式和数据

DROP TABLE IF EXISTS tab;

CREATE TABLE tab (
  `ID` int(11) NOT NULL,
  `BET1` VARCHAR(100) DEFAULT NULL,
  `BET2` VARCHAR(100) DEFAULT NULL,
  `BET3` VARCHAR(100) DEFAULT NULL,
  `BET4` VARCHAR(100) DEFAULT NULL,
  `string_col` VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM tab;
INSERT INTO tab VALUES
    (10, NULL, NULL, NULL, NULL, 'A'),
    (10, 'Alice', NULL, NULL, NULL, 'B'),
    (10, 'Alice', 'Bob', NULL, NULL, 'C'),
    (10, 'Alice', 'Bob', 'Charlie', NULL, 'D'),
    (10, 'Alice', 'Bob', 'Charlie', 'Dave', 'E');

SELECT * FROM tab;
+----+-------+------+---------+------+------------+
| ID | BET1  | BET2 | BET3    | BET4 | string_col |
+----+-------+------+---------+------+------------+
| 10 | NULL  | NULL | NULL    | NULL | A          |
| 10 | Alice | NULL | NULL    | NULL | B          |
| 10 | Alice | Bob  | NULL    | NULL | C          |
| 10 | Alice | Bob  | Charlie | NULL | D          |
| 10 | Alice | Bob  | Charlie | Dave | E          |
+----+-------+------+---------+------+------------+

更新。

UPDATE tab
set 
    string_col = @ORIG_STRING_COL := (@UPDATED := string_col),
    BET1 = IF(BET1 IS NULL, @UPDATED := 'Susan', BET1),
    BET2 = IF(@UPDATED != 'Susan' AND BET2 IS NULL, @UPDATED := 'Susan', BET2),
    BET3 = IF(@UPDATED != 'Susan' AND BET3 IS NULL, @UPDATED := 'Susan', BET3),
    BET4 = IF(@UPDATED != 'Susan' AND BET4 IS NULL, @UPDATED := 'Susan', BET4),
    string_col = @ORIG_STRING_COL
WHERE ID = 10;

结果

SELECT * FROM tab;
+----+-------+-------+---------+-------+------------+
| ID | BET1  | BET2  | BET3    | BET4  | string_col |
+----+-------+-------+---------+-------+------------+
| 10 | Susan | NULL  | NULL    | NULL  | A          |
| 10 | Alice | Susan | NULL    | NULL  | B          |
| 10 | Alice | Bob   | Susan   | NULL  | C          |
| 10 | Alice | Bob   | Charlie | Susan | D          |
| 10 | Alice | Bob   | Charlie | Dave  | E          |
+----+-------+-------+---------+-------+------------+

2. 对每条记录执行一次 UPDATE

使用以下伪代码，你可以实现它。

$res = mysqli_query(SELECT pk_column FROM tab WHERE ID = 10);

while ($row = mysqli_fetch_assoc($res))
{
    $pk_col = $row['pk_column'];

    mysqli_query("SET @UPDATED := ''");

    mysqli_query("UPDATE tab
        SET 
            BET1 = IF(BET1 IS NULL, @UPDATED := 'Susan', BET1),
            BET2 = IF(@UPDATED = '' AND BET2 IS NULL, @UPDATED := 'Susan', BET2),
            BET3 = IF(@UPDATED = '' AND BET3 IS NULL, @UPDATED := 'Susan', BET3),
            BET4 = IF(@UPDATED = '' AND BET4 IS NULL, @UPDATED := 'Susan', BET4)
        WHERE pk_col = $pk_column
    ");

}

前置代码可以在客户端实现，也可以作为存储过程。