Oracle trying to update a table by joining a non indexed table

Question

I tried looking for a similar example to my problem but could not reproduce the solution to my success.

I have 2 tables, Controller and Actions.

The Actions table has the columns Step, Script, Description, Wait_Until and Ref_Code.

The Controller table can only be joined on the Action table by the Ref_Code.

The Action table cannot have a PK because for each Ref_Code there is a Step to be taken.

Im getting an error when trying to update the Controller table using a merge statement:

ORA-30926: unable to get a stable set of rows in the source tables

My merge statement is as follows:

MERGE INTO DSTETL.SHB_FTPS_CONTROLLER ftpsc
     USING (SELECT DISTINCT FTPSC.SESSION_ID,
                            FTPSC.ORDER_DATE,
                            sa.step,
                            sa.next_step,
                            LAST_ACTION_TMSTMP,
                            SA.ACTION_SCRIPT,
                            sa.ref_code,
                            SA.WAIT_UNTIL
              FROM DSTETL.SHB_FTPS_CONTROLLER ftpsc, DSTETL.SHB_ACTIONS sa
             WHERE     SA.REF_CODE = FTPSC.REF_CODE
                   AND SA.STEP > ftpsc.curr_step
                   AND sa.step = ftpsc.next_step) v1
        ON (v1.REF_CODE = FTPSC.REF_CODE)
WHEN MATCHED
THEN
   UPDATE SET FTPSC.LAST_ACTION_TMSTMP = CURRENT_TIMESTAMP,
              ftpsc.next_step = v1.next_step,
              ftpsc.curr_step = v1.STEP,
              ftpsc.action_script = v1.action_script
           WHERE CURRENT_TIMESTAMP >= v1.LAST_ACTION_TMSTMP + v1.WAIT_UNTIL;

COMMIT;

I tried doing this using a normal update as well but Im getting ORA-01732: data manipulation operation not legal on this view.

UPDATE (SELECT FTPSC.SESSION_ID,
                        FTPSC.ORDER_DATE,
                        FTPSC.CURR_STEP,
                        FTPSC.NEXT_STEP,
                        FTPSC.ACTION_SCRIPT,
                        sa.step,                                    --New Step
                        sa.next_step AS "NNS",                 --New Next Step
                        FTPSC.LAST_ACTION_TMSTMP,
                        SA.ACTION_SCRIPT AS "NAS",         --New action script
                        sa.ref_code,
                        SA.WAIT_UNTIL
          FROM    DSTETL.SHB_FTPS_CONTROLLER ftpsc
               LEFT JOIN
                  DSTETL.SHB_ACTIONS sa
               ON     SA.REF_CODE = FTPSC.REF_CODE
                  AND SA.STEP > ftpsc.curr_step
                  AND sa.step = ftpsc.next_step) t
   SET t.curr_step = t.step,
       t.LAST_ACTION_TMSTMP = CURRENT_TIMESTAMP,
       t.next_step = t."NNS",
       t.action_script = t."NAS";

       COMMIT;

Any advice would be appreciated, I already understand this is because the Action table has multiple Ref_Codes but REF_CODE||STEP is unique. And the output of:

SELECT DISTINCT FTPSC.SESSION_ID,
                                FTPSC.ORDER_DATE,
                                sa.step,
                                sa.next_step,
                                LAST_ACTION_TMSTMP,
                                SA.ACTION_SCRIPT,
                                sa.ref_code,
                                SA.WAIT_UNTIL
                  FROM DSTETL.SHB_FTPS_CONTROLLER ftpsc, DSTETL.SHB_ACTIONS sa
                 WHERE     SA.REF_CODE = FTPSC.REF_CODE
                       AND SA.STEP > ftpsc.curr_step
                       AND sa.step = ftpsc.next_step;

Is how I want the Controller table to be updated like.

Thanks in advance.

kfinity · Accepted Answer · 2019-02-28T16:47:26.773

It sounds like what you want to do is: update each row in the Controller table with the matching "next step" details from the Actions table. But your Merge statement is querying the Controller table twice, which confuses things.

Is this what you're trying to do?

MERGE INTO DSTETL.SHB_FTPS_CONTROLLER ftpsc
     USING (SELECT 
                    step,
                    next_step,
                    ACTION_SCRIPT,
                    ref_code,
                    WAIT_UNTIL
              FROM DSTETL.SHB_ACTIONS
             ) sa
        ON (sa.REF_CODE = FTPSC.REF_CODE)
WHEN MATCHED
THEN
   UPDATE SET FTPSC.LAST_ACTION_TMSTMP = CURRENT_TIMESTAMP,
              ftpsc.next_step = sa.next_step,
              ftpsc.curr_step = sa.STEP,
              ftpsc.action_script = sa.action_script
         WHERE CURRENT_TIMESTAMP >= ftpsc.LAST_ACTION_TMSTMP + sa.WAIT_UNTIL
           AND SA.STEP > ftpsc.curr_step
           AND sa.step = ftpsc.next_step;

EDIT: updated query

EDIT2: So, in your original query, in the USING section you were selecting the rows in the Controller table that you wanted to update... but you never joined those rows to the Controller table from the MERGE INTO section to match them up. Having the same alias "ftpsc" just made it less clear that they're two separate objects in the query, and which one you wanted to update.

Honestly I don't really understand why Oracle won't let you update columns that appear in the USING..ON clause. It apparently works fine in SQL Server.

Just tried this and now Im getting ORA-38104: Columns referenced in the ON Clause cannot be updated: "FTPSC"."NEXT_STEP" — Ali E, Feb 28 '19 at 08:08
Oh right - I moved those join conditions to the where clause, is that any better? — kfinity, Feb 28 '19 at 15:02
Works like a charm mate! Do you mind explaining what you meant by trying to update the Controller table twice? Why I couldnt put the columns I wanted to update in the "USING" clause? — Ali E, Feb 28 '19 at 16:14

Oracle trying to update a table by joining a non indexed table

1 Answers1