How to update multiple rows using a sub-query and order by in the sub-query?

Question

I am trying to update a table using a sub-query, however the sub-query contains multiple joins as I am getting data from multiple tables, and as a business requirement I am forced to add an Order by in the sub-query to sort elements based on the primary key, if order by is not added then the output is not accurate. A simple example without the joins of what I am trying to do is:

UPDATE EMPLOYEES e
SET (e.JOB, e.SAL, e.COMM) = 
(
SELECT p.JOB, p.SAL, p.COMM FROM EMP p WHERE p.ENAME = e.ENAME ORDER BY p.DEPTNO
)
WHERE DEPTNO = 30;

The main issue is not being able to use Order by in sub-query.

This throws an error message:

Error at line 4/80: ORA-00907: missing right parenthesis
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 847
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 833
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 1903

If I remove the Order by from the sub-query then I get no error message, however my result is not the expected. How can I achieve this?

Answer 1

It is syntactically invalid to have an ORDER BY clause in the outer-most sub-query of a correlated sub-query as the order of the results does not matter as there should only be a single matching row for the sub-query. Therefore the general answer to your question is that it is impossible to have an ORDER BY clause because the syntax forbids it.

Since Oracle 12, there is an exception which allows an ORDER BY clause in a correlated sub-query and that is when you also use FETCH FIRST ROW ONLY to guarantee that the sub-query returns only a single row.

So, if you are getting multiple rows for the sub-query and you only want the first row then, from Oracle 12, you can use:

UPDATE EMPLOYEES e
SET (e.JOB, e.SAL, e.COMM) = (SELECT p.JOB, p.SAL, p.COMM
                              FROM   EMP p
                              WHERE  p.ENAME = e.ENAME
                              ORDER BY p.DEPTNO
                              FETCH FIRST ROW ONLY)
WHERE DEPTNO = 30;

However, it seems more likely that you would want to use something else to correlate the queries so that you only ever get a single row.

as a business requirement I am forced to add an Order by in the sub-query to sort elements based on the primary key

This does not appear to be what you are doing as you are sorting by the department number, DEPTNO, and it seems to be unlikely that the primary key for an employee is the number of their department.

db<>fiddle here