使用for update的条件

  • 时间:
  • 浏览:264
  • 来源:成都艾邦软件开发

为什么80%的码农都做不了架构师   

示例场景

(1) 数据表table_001中有如下字段column_01, column_02, column_03, column_04

(2) 由于业务变更增加了如下字段column_10 column_11 column_12 column_13

(3) 对于之前表中存在的记录需要根据“column_01, column_02, column_03, column_04”的字段值来填充column_10 column_11 column_12 column_13的值。

示例语句

update table_001 
set column_01  
(case when column_100 then 0
when column_10120 then 1
when column_10120 and column_10140 then 2
when column_10140 and column_10160 then 3
when column_10160 and column_10180 then 4
when column_10180 then 5
end),
column_02
(case when column_110 then 0
when column_1180 then 1
when column_1180 and column_1190 then 2
when column_1190 and column_11100 then 3
when column_11100 and column_11110 then 4
when column_11110 then 5
end),
column_03  
(case when column_120 then 0
when column_12140 then 1
when column_12140 then 2
end),
column_04  
(case when column_130 then 0
when column_1390 then 1
when column_1390 then 2
end);

参考/postgresqlzhi-shi-zheng-li/

/sz_bdqn/article/details/8267691

blog/909571

UPDATE cw_party tp,
cw_shop ts
SET tp.state 3,
ts.bonus_average CASE
WHEN ts.bonus_average 0 THEN
(50 ts.bonus_average) / 2
ELSE
50
END,
tp.update_time 1510629839,
ts.update_time 1510629839
WHERE
tp.id 470
AND ts.id 49