mysql - 根据旧值更新多个字段

Question

我已经测试了这个查询(见下文)但它不起作用

我有这张表:

id     A      B      C
-----------------------
1      1      2     
2             3     
3      2             4
4      6      2      1
-----------------------

我想在第一个空白字段中将 A、B 或 C 设置为 4，除非 4 已经在 A、B 或 C 中。

So if (condition 1) A!=4 and B!=4 and C!=4 => (condition 2) if A!=""goto B, if B!=""跳转到 C, if C!=""跳过更新。

In my example 
row 1: C => 4
row 2: A => 4
row 3: not updated (4 allready exists)
row 4: not updated (no empty field)

我提出的查询:

UPDATE table SET 
    A=IF(A!="",A,4),                 //1:
    B=IF(A="" OR B!="",B,4),         //2:
    C=IF(A="" OR B="",C,4)           //3:
WHERE (A!="" OR B!="" OR C!="")      //4:
       AND (A!=4 AND B!=4 AND C!=4)  //5: 

       // 1: if A="", make it 4, else keep value  
       // 2: if A was "" it should be 4 by now, let B be B, else make B 4
       // 3: if A was "" or B was "", one of them should be 4 by now, else make C 4
       // 4: eliminates row 4 (check if there's a free field)
       // 5: eliminates row 3 (condition 1)

此查询是否有效(尤其是第 2 部分和第 3 部分)取决于 mySQL 的作用:它会查看 IF 语句中字段的 OLD 值，还是会首先更新一个字段并然后将新值带到下一部分？测试朗姆酒失败，它将所有空字段更新为 4，表明它考虑了新值。

那么我的问题是:我该怎么做才能使查询满足我的条件？

[编辑]

这不是最优雅的查询，但它有效:

UPDATE table SET 
    A=IF( A<>"",A,4),                
    B=IF( (A="" OR A=4) OR B<>"",B,4),        
    C=IF( (A="" OR A=4) OR (B="" OR B=4) OR C<>"",C,4)         
WHERE (A!="" OR B!="" OR C!="")     
       AND (A!=4 AND B!=4 AND C!=4) 

好像mySQL把A的新值传给了B的IF语句，又把A和B的新值传给了C

Answer 1

尝试

UPDATE table SET 
    A=IF(A is null,4,A),   -- if a is null, set it to 4            
    B=IF(A is not null and b is null,4,B),       -- if A did not meet the condition to set to 4 but B is null, set B to 4
    C=IF(A is not null and B is not null and C is null ,4,C) --  if A or B did not meet the condition to be set to 4 and C is null, set c to 4
WHERE (a+b+c is null)     --- short of "a is null or b is null or c is null"
       AND (A!=4 AND B!=4 AND C!=4)