Oracle update invalid identifier on Update

Question

I get an 'invalid identifier' exception when I run the next script. As I have seen on this link it should work.

It cannot reference to the 'a2' table under the secound selection, but it should update the row with the related value.

update auto a2 set uuid = 
    (select uuid from (
        select c.uuid, c.pk from color c
            join sit s on s.pk = c.sit_fk
            --where s.auto_fk = auto.pk
            join auto m on m.pk = s.auto_fk
            where m.pk = a2.pk
            group by c.pk, c.uuid
            order by c.pk desc
        )
    where rownum = 1)

Not sure if you want all those joins to get the required value to update at all. show some sample rows of each table and expected result on update so that we can try to provide a better solution to you. — Kaushik Nayak, Dec 06 '18 at 09:02
The linked query is wrong and will raise the same exception. You cannot reference an alias more than 1 nested query deep. — MT0, Dec 06 '18 at 09:11

score 0 · Accepted Answer · answered Dec 06 '18 at 09:04

Reference the a2 alias in the outer level of the correlated query, rather than the nested level.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE auto ( pk, uuid ) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 2, 2 FROM DUAL;

CREATE TABLE color ( pk, uuid, sit_fk ) AS
  SELECT 1, 2, 1 FROM DUAL UNION ALL
  SELECT 2, 1, 2 FROM DUAL;

CREATE TABLE sit ( pk, auto_fk ) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 2, 2 FROM DUAL;

Query 1:

update auto a2 set uuid = 
    (select uuid from (
        select c.uuid, c.pk, m.pk AS apk from color c
            join sit s on s.pk = c.sit_fk
            --where s.auto_fk = auto.pk
            join auto m on m.pk = s.auto_fk
            group by m.pk, c.pk, c.uuid
            order by m.pk, c.pk desc
        )
    where rownum = 1
    and a2.pk = apk
)

Results:

Query 2:

SELECT *
FROM   auto

Results:

| PK | UUID |
|----|------|
|  1 |    2 |
|  2 |    1 |

Oracle update invalid identifier on Update

1 Answers1