CODEWITHSUNDEEP
sqloracle-database
VJay
VJay

Reputation: 49

Update a table with subquery

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

I am using the query :

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 )

Please assist.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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
                );

Upvotes: 2

Related Questions