UPDATE mytalbe SET
propertyA = CASE id
WHEN1THEN3WHEN2THEN4WHEN3THEN5END,
propertyB = CASE id
WHEN1THEN'aaa'WHEN2THEN'bbb'WHEN3THEN'ccc'ENDWHERE id IN (1,2,3);
另一种情况是要判断某个字段是否满足某个表达式,来做相应修改,我今天遇到的情况就是这样。如下:
UPDATE mytable
SET propertyA =
CASEWHENleft(number, 1)='1'THEN1 -- number这个varchar类型的字段开头为“1”,则设置propertyA为1WHENleft(number, 1)='2'THEN2 -- number这个varchar类型的字段开头为“2”,则设置propertyA为2WHENleft(number, 1)='3'THEN3WHENleft(number, 1)='4'THEN4WHENleft(number, 1)='5'THEN5WHENleft(number, 1)='5'THEN6ELSE0END,
propertyB =
CASEWHEN CARGO_TAX_RATE in (0.17,0.13,0.11,0.06) THEN1 -- 17%或13%或11%或6%,1WHEN CARGO_TAX_RATE in (0.05,0.04,0.03) THEN2 -- 5%或4%或3%,2ELSE4 -- 0% 或者其他,默认免税
END,
propertyC = 1, -- propertyC这个属性默认为1
propertyD = 0 -- propertyD这个属性默认为0WHERE
EID IN (277)
AND NATURE_OF_THE_INVOICE_LINE_NEW IN (0, 4, 8)
AND BILLING_DATE >= "2017-03-01"AND BILLING_DATE <= "2018-03-05"AND IS_DELETE = 0
需求是根据某个字段的值不同,修改相对应的另一个字段,于是就用到了mysql的case when,在此记录一下。隐私起见随便修改了表名和字段名。有种简单的情况是只需要判断某个字段的值就可以确定另一个字段的值:UPDATE mytalbe SET propertyA = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHE