mysql - mysql SET CASE THEN THEN AND AND预期的结果

初始mysql表

 RowIdPaidusedUnique | TypeOfBookkeepingRecord
 ---------------------------------------------
     1               |     45
     2               |     45
     3               |     4

查询为

UPDATE 2_1_paidused
SET TypeOfBookkeepingRecord = CASE RowIdPaidusedUnique
WHEN ? THEN ? WHEN ? THEN ?
AND TypeOfBookkeepingRecord <> ?
END;

数组为

Array
(
[0] => 1
[1] => 5
[2] => 2
[3] => 5
[4] => 4
)

想得到

其中RowIdPaidusedUnique为1而TypeOfBookkeepingRecord不是4，则将TypeOfBookkeepingRecord更改为5

其中RowIdPaidusedUnique是2而TypeOfBookkeepingRecord不是4，则将TypeOfBookkeepingRecord更改为5

但是结果

 RowIdPaidusedUnique | TypeOfBookkeepingRecord
 ---------------------------------------------
     1               |     5
     2               |     1
     3               |

AND TypeOfBookkeepingRecord <> ?似乎有问题

我需要更改为类似

WHEN ? AND TypeOfBookkeepingRecord <> ? THEN ?

最佳答案

您可能想像下面那样使用CASE语句，并将条件TypeOfBookkeepingRecord is not 4放在WHERE中。

在此处查看演示小提琴http://sqlfiddle.com/#!2/9c831/1

UPDATE table1
SET TypeOfBookkeepingRecord =
(CASE WHEN RowIdPaidusedUnique = 1 then 5
 WHEN  RowIdPaidusedUnique = 2 then 1 END)
WHERE TypeOfBookkeepingRecord != 4

关于mysql - mysql SET CASE THEN THEN AND AND预期的结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23821011/