Update oracle table referencing updated table / field? (Correlated update?)

Question

I have a task manager which drops some info in a log table simmilar to this:

processId    owner      state            state_date
------------------------------------------------------------

1-88D6PJ     group1     OPENED           14/08/2013 14:57:44
1-88D6PJ     group2     ASSIGNED         14/08/2013 14:58:36
1-88D6PJ     group3     ASSIGNED         15/08/2013 11:26:45
1-88D6PJ     group2     ASSIGNED         17/08/2013 17:20:13
1-88D6PJ     group3     ASSIGNED         19/08/2013 09:05:12
1-88D6PJ     null       FINISHED         22/08/2013 12:13:59

When a task is finished, the owner is not stored. Fixed it in the code, but now I would like to fix old entries.

To do it, for every entry with null owner,I need to search the penultimate entry to take the owner,which will be the same who closes the task.

I was trying to make an approach, and seems that something simmilar to this could work:

UPDATE tasks.tasks_log t1
   set (t1.owner) =
       (SELECT owner
          FROM (SELECT owner
                  FROM tasks.tasks_log t2
                 WHERE t1.processId = ep2.processId
                   and state not in ('FINISHED', 'CANCELLED')
                   and state_date is not null
                 ORDER BY state_date DESC)
         WHERE rownum = 1)

Maybe not elegant or efficcient, but seems it could work, but when i run it I receive an ORA-00904: "t1"."processId"invalid identifier.

I suppossed that is not possible make reference to the updated table identifier, but after check the oracle documentation and some posts, I see similar UPDATES suppossed to work.

Is possible to make that reference? or I must totally change the approach to make this update?

score 1 · Accepted Answer · answered May 11 '17 at 11:27

There are several ways to do this (such as merge). But with your approach, you can use keep:

UPDATE tasks.tasks_log t1
   set (t1.owner) = (SELECT MAX(t2.owner) KEEP (FIRST DENSE_RANK ORDER BY BY state_date DESC)
                     FROM tasks.tasks_log t2
                     WHERE t1.processId = ep2.processId AND
                           t2.state not in ('FINISHED', 'CANCELLED') AND
                           t2.state_date is not null
                    );

Thank you very much. Worked fine, and was useful to learn a bit more since it's the first time I see this. — Mortuk, May 11 '17 at 14:41

Update oracle table referencing updated table / field? (Correlated update?)

1 Answers1