用子查询更新表 [英] Update a table with subquery

问题描述

我必须更新表的STATUS列

I have to update the STATUS column of the table

seq Req source  region count STATUS
1   1   C001    ABC     0     NULL
2   1   C001    DEF     0     NULL
3   1   C001    GHI     10    NULL

基于下表中的限制和比较，

Based on the limit and comparison on the below table,

source  description symbol limit
----    ----------- ------ -----
c001    pass         >      10
c002    fail         =      0

我正在使用查询:

update table1 
   set a.STATUS = (select case b.symbol when '>' then case when a.c2 > b.limit then 'PASS' 
            else 'FAIL' end when '<' then case when a.c2 < b.limit 
            then 'PASS' else 'FAIL' end when '=' then case when a.c2 = b.limit then 'PASS' 
            else 'FAIL' end end from table1 a join table2 b on a.source=b.source )

请协助.

推荐答案

我认为您想要一个相关的子查询:

I think you want a correlated subquery:

update table1  a
   set STATUS = (select (case when b.symbol = '>' and a.c2 > b.limit or
                                   b.symbol = '<' and a.c2 < b.limit or
                                   b.symbol = '=' and a.c2 = b.limit 
                              then 'PASS' else 'FAIL'
                         end)
                 from table2 b 
                 where a.source = b.source
                );

这篇关于用子查询更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！