使用多个条件和多行更新

作者: Jacob
发布时间: 2025-01-09 09:12:26 (1月前)
转自：

4 条回复

0#
回复此人
妖邪 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 使用带有条件聚合的子查询： </p> <pre> <code> UPDATE test_table tt SET tt.product_case = COALESCE(ct.pc, tt.product_case), tt.customer_case = COALESCE(ct.cc, tt.customer_case) FROM (SELECT ct.MAINRECORD, MAX(CASE WHEN ct.TYPE = 'PC' THEN ct.ID END) as pc, MAX(CASE WHEN ct.TYPE = 'CC' THEN ct.ID END) as cc FROM CASETABLE ct WHERE ct.STATUS = 'ACTIVE' GROUP BY ct.MAINRECORD ) ct WHERE ct.MAINRECORD = tt.main_id ND (ct.pc is not null or ct.cc is not null); </code> </pre> </DIV>

编辑
1#
回复此人
v-star*위위 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 你可以尝试下面 - </p> <pre> <code> UPDATE test_table tt SET tt.product_case = (SELECT CASE WHEN ct.TYPE = 'PC' THEN ct.ID ELSE tt_.product_case END FROM CASETABLE ct WHERE ct.MAINRECORD = tt.main_id), tt.customer_case=(SELECT CASE WHEN ct.TYPE = 'CC' THEN ct.ID ELSE tt_.product_case END FROM CASETABLE ct WHERE ct.MAINRECORD = tt.main_id) WHERE EXISTS( SELECT 1 FROM CASETABLE ct WHERE ct.MAINRECORD = tt.main_id and STATUS = 'ACTIVE') </code> </pre> </DIV>

编辑
2#
回复此人
荧惑 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 请试试这个 </p> <pre> <code> UPDATE tt SET tt.product_case = CASE WHEN ct.TYPE = 'PC' THEN ct.ID ELSE tt_.product_case END, tt.customer_case = CASE WHEN ct.TYPE = 'CC' THEN ct.ID ELSE tt.customer_case END FROM test_table tt INNER JOIN CASETABLE ct ON ct.MAINRECORD = tt.main_id WHERE ct.STATUS = 'ACTIVE' </code> </pre> </DIV>

编辑

登录后才能参与评论