$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";

$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,BET4=NULL.

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

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

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

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

$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;

您正在寻找“UPDATE First NULL column”.我认为这可以使用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),(10,32,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

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.使用一些string_column

我认为你有两个选择

>使用字符串列而不是ID列

>优点：可以使用一个UPDATE实现
>缺点：如果没有更多的字符串列,则无法使用

>为每行执行UPDATE

> PRos：即使没有更多的字符串列也可以使用
> cons：执行几次UPDATE,如果没有PK则不能使用.

我想知道你是否有像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,'A'),'Alice','B'),'Bob','C'),'Charlie','D'),'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

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,BET2),BET3 = IF(@UPDATED != 'Susan' AND BET3 IS NULL,BET3),BET4 = IF(@UPDATED != 'Susan' AND BET4 IS NULL,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,BET2 = IF(@UPDATED = '' AND BET2 IS NULL,BET3 = IF(@UPDATED = '' AND BET3 IS NULL,BET4 = IF(@UPDATED = '' AND BET4 IS NULL,BET4)
        WHERE pk_col = $pk_column
    ");

}

前面的代码可以实现客户端或存储过程.