[SOLVED] Oracle merge sql doesn't work when update with where condtions

Oracle merge sql doesn't work when update with where condtions

My problem is that an error occurred when I update with where conditions.

The error message is 00936. 00000 - "missing expression"

If I remove this line where q1.SALE_QTY <> -9 , then that sql works.

Database version : oracle 9i SQL:

merge into TBL_QTY q1
using (
select 'A111' as prod_no
       ,'BAT0000171624' as part_batch_no
       ,'172' as cond1_type
       ,'20201225' as beg_dt  
      ,'20201225' as end_dt
      , 17 as obtain_qty
      , 17 as sale_qty ) q2
      on　
(  q1.PROD_NO = q2.PROD_NO
  and q1.PART_BATCH_NO = q2.PART_BATCH_NO
  and q1.COND1_TYPE = q2.COND1_TYPE
  and q1.BEG_DT = q2.BEG_DT
  and q1.END_DT= q2.END_DT)
WHEN MATCHED THEN 
   update  SET q1.OBTAIN_QTY    = 15     
               ,q1.SALE_QTY      = 15 
               where q1.SALE_QTY <> -9
WHEN NOT MATCHED THEN
   INSERT (PROD_NO, PART_BATCH_NO, COND1_TYPE, BEG_DT
           ,END_DT , OBTAIN_QTY   , SALE_QTY           )
   VALUES (q2.PROD_NO, q2.PART_BATCH_NO, q2.COND1_TYPE, q2.BEG_DT
           ,q2.END_DT , q2.OBTAIN_QTY   , q2.SALE_QTY           );

Solution

One way to achieve the same behavior is to use CASE..WHEN as follows in UPDATE clause of the MERGE statement.

...
...
WHEN MATCHED THEN 
   update  SET q1.OBTAIN_QTY = CASE WHEN q1.SALE_QTY <> -9 THEN 15 ELSE q1.OBTAIN_QTY END
               ,q1.SALE_QTY  = CASE WHEN q1.SALE_QTY <> -9 THEN 15 ELSE q1.SALE_QTY END
              -- where q1.SALE_QTY <> -9
WHEN NOT MATCHED THEN
...
...