ORA-00904 - Anyone else running into this?

[LinuxGuy]

Anyone else having a problem with fields that exist being rejected with a ORA-00904 error?

Example:

UPDATE table1
SET table1.field1 = table2.field1
WHERE table1.field2 = table2.field2;

aka. ORA-00904 "table2","field2","Invalid Identifier".

I am running into this using MERGE INTO, SELECT
INTO, and UPDATE commands through PLDeveloper
in a standard SQL Window. Is this a known bug
in Developer???

The tables are valid, the fields are valid, the
table alias is valid, and the syntax of the
statements is simple and valid. Yet the system seems to be rejecting it. Anyone else encounter this problem?

Any help would be appreciated.

 

[gulhaugen]

When using merge, any field in the where clause cannot appear in the update statement ( if I remember correctly)

 

[William Robertson]

UPDATE table1
SET table1.field1 = table2.field1
WHERE table1.field2 = table2.field2;

and the syntax of the
statements is simple and valid.

That is not valid. There needs to be a subquery e.g.

UPDATE table1 t1
SET    field1 =
       ( SELECT field1
         FROM   table2
         WHERE  field2 = t1.field2 )
WHERE  EXISTS
       ( SELECT NULL
         FROM   table2
         WHERE  field2 = t1.field2 );

Or a join:

UPDATE
( SELECT t1.field1 AS oldvalue
       , t2.field1 AS newvalue
  FROM   table1 t1
       , table2 t2
  WHERE  t2.field2 = t1.field2 )
SET oldvalue = newvalue;