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

Question

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

The `merge` statement simply didn't support a `WHERE` clause in the UPDATE part in Oracle 9 https://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_915a.htm#2084365 — , Mar 17 '20 at 06:42
Thank you!! Is there any way can help to achieve my purpose? To update with conditions. — mike.jiang, Mar 17 '20 at 06:47
Oracle 10g have the option to use WHERE clause with merge statement. You can refer - https://oracle-base.com/articles/10g/merge-enhancements-10g. So another option is to have upgraded version Since 9i is too old to use now. — Ankit Bajpai, Mar 17 '20 at 06:58
@AnkitBajpai: "*Since 9i is too old to use now*" - and so is Oracle 10 (and 11) — , Mar 17 '20 at 08:36
@a_horse_with_no_name, I meant to say that, Oracle 10g have introduced the usage of where clause in merge statement. — Ankit Bajpai, Mar 17 '20 at 09:28

Popeye · Accepted Answer · 2020-03-17T08:17:57.567

0

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
...
...

Cheers!!

edited Mar 17 '20 at 08:17

answered Mar 17 '20 at 08:11

Popeye

35,427
4
10
31

it almost achieves my purpose. But when the case not in "case when", there will be an "cannot udpate to null" error. Actually I want is that do nothing when qty != -9 – mike.jiang Mar 19 '20 at 09:43

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

1 Answers1