Update query identifier

Question

UPDATE Table1 a
SET a.Col2 = (
    SELECT SubStr(Trim(b.Col2),-10,10)
    FROM Table2 b
    WHERE (InStr(SubStr(Trim(b.Col2),-5,5),'/')=1 
        AND InStr(SubStr(Trim(b.Col2),1,(Length(Trim(b.Col2))-10)),'/')=0 
        AND SubStr(b.Col1,1,2)='01'
        AND b.Col3 != 000103))
WHERE SubStr(b.Col1,-8,8)=SubStr(a.Col1,1,8)
    AND a.Col3 = 1;

Error at Command Line:9 Column:14 Error report: SQL Error: ORA-00904: "B"."Col1": invalid identifier 00904. 00000 - "%s: invalid identifier"

Line 9 is WHERE SubStr(b.Col1,-8,8)=SubStr(a.Col1,1,8)

You're referencing the inner query in the outer. Don't, move this condition into the inner query. — Ben
the subquery you use in SET a.birthdate = (...) cannot have any outer reference to it. — Sebas

Chorel Chorel · Accepted Answer · 2013-06-03T21:44:25

You can try to MERGE:

MERGE INTO Table1 a
USING (
    SELECT *
    FROM Table2 b
    WHERE (InStr(SubStr(Trim(b.Col2),-5,5),'/')=1 
        AND InStr(SubStr(Trim(b.Col2),1,(Length(Trim(b.Col2))-10)),'/')=0 
        AND SubStr(b.Col1,1,2)='01'
        AND b.Col3 != 000103)
) c ON (SubStr(c.Col1,-8,8)=SubStr(a.Col1,1,8))
WHEN MATCHED THEN
    UPDATE SET a.Col2 = SubStr(Trim(c.Col2),-10,10)
        WHERE a.Col3 = 1;

Update query identifier

1 Answers