Kirti Kirti - 5 months ago 16
SQL Question

How to use CASE expression to update a table with inner queries

I have used the following update query :

UPDATE datA_table T
SET T.VALUE=
(SELECT
CASE
WHEN t3.h1 =(t3.h2) and t3.h1=(t3.h3) THEN t3.h1
ELSE
Case
wHEN T3.h1 < > T3.h2 THEN T3.h2
ELSE
cASE
wHEN T3.h1 < > T3.h3 THEN T3.h3
eND
eND
END
from datA_table t3)T1
where t.time=t1.time and t.name=t1.name


But this is giving the following error:


Error report - SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"


Is there any way to resolve this issue?

Answer

You can have many WHEN in each CASE, which is also likely to perform better than nested CASEs. I'm unfamiliar with the way you linked the updated table with the subselect (normally you can't refer to internal aliases in a subquery from outside), and don't know if it works at all, so I coded it in a way I know for sure it works:

UPDATE datA_table T
SET T.VALUE=
  (SELECT   
      CASE 
         WHEN t3.h1 =(t3.h2) and t3.h1=(t3.h3) THEN t3.h1
         WHEN T3.h1 < > T3.h2 THEN T3.h2
         wHEN T3.h1 < > T3.h3 THEN T3.h3
      END
      from datA_table t3
      where t.id=t3.id AND T.name=T3.name
  )
where <your condition for datA_table rows to be updated>


Update

I just noticed that both the table being modified and the table in the subquery are the same table, and that the joining condition is likely to be a key. Therefore, there is no need to specify a subquery at all. The following simpler UPDATE will do the same, but likely much faster (I also improved the CASE logic, as the first test was superfluous):

UPDATE datA_table
SET VALUE = CASE 
              WHEN h1 <> h2 THEN h2
              WHEN h1 <> h3 THEN h3
              ELSE h1
            END
WHERE <your condition for rows to be updated>