It is curious to convert Mysql to Oracle

Question

this is mysql code.

UPDATE ORDER_ITEM OI
INNER JOIN ORDER_PAYMENT OP
ON OI.ORDER_PAYMENT_ID = OP.ORDER_PAYMENT_ID
SET OI.ORDER_STATUS    = '10',
  OI.PAY_DATE          = '20150101',
  OP.PAY_DATE          = '20150101'
WHERE OI.ORDER_STATUS        = '0'
AND OP.AMOUNT                = 3333
AND OP.REMAINING_AMOUNT      = 0
AND OP.ORDER_PAYMENT_ID      = 9999

convert Oracle Error => ORA-00971: missing SET keyword

I would like to know what to convert this statement to Oracle.

PS. The multi- table updates available in Oracle?

http://www.geeksengine.com/article/oracle-cross-table-update.html — Anik Islam Abhi, Oct 28 '15 at 04:42

score 0 · Accepted Answer · answered Oct 28 '15 at 06:23

Definitely Oracle doesn't allow to update two tables in one SQL statement. So you need 3 statements for your case:

-- This query locks target rows in both tables.
-- It's required to be sure that rows wasn't changed in other sessions
-- between the first and second UPDATE statements
SELECT *
  FROM order_item oi
 INNER JOIN order_payment op
    ON oi.order_payment_id = op.order_payment_id    
 WHERE oi.order_status = '0'
   AND op.amount = 3333
   AND op.remaining_amount = 0
   AND op.order_payment_id = 9999
   FOR UPDATE NOWAIT

-- first update
UPDATE order_item
   SET order_status = '10',
       pay_date = '20150101'
 WHERE rowid IN (SELECT oi.rowid
                      FROM order_item oi
                     INNER JOIN order_payment op
                        ON oi.order_payment_id = op.order_payment_id                                
                     WHERE oi.order_status = '0'
                       AND op.amount = 3333
                       AND op.remaining_amount = 0
                       AND op.order_payment_id = 9999);

--second update
UPDATE order_payment
   SET pay_date = '20150101'
 WHERE rowid IN (SELECT op.rowid
                      FROM order_item oi
                     INNER JOIN order_payment op
                        ON oi.order_payment_id = op.order_payment_id                                
                     WHERE oi.order_status = '0'
                       AND op.amount = 3333
                       AND op.remaining_amount = 0
                       AND op.order_payment_id = 9999);

Using PL/SQL we can simplify this code a little bit, but still we'll have to write a SELECT FOR UPDATE and one UPDATE per each joined table.

It is curious to convert Mysql to Oracle

1 Answers1