Invalid identifier SQL error displaying upon executing a update query for a tables in different schema in Oracle database

Question

Here I am using Oracle SQL developer and I need to update a table in an another schema. Here is the update query which I have written.

update dcs.lte_pin_register set pin = '' 
where dcs.lte_pin_register.subscriber_seqno = dcs.subscriber.subscriber_seqno 
and dcs.subscriber.sub_number = '?'; 

In the above query,

• dcs -> Refer to another schema in the database
• lte_pin_register and subscriber are two tables in the dcs schema
• subscriber_seqno and sub_number are the columns in the respective tables.

However when I am going to execute the above query using a valid sub_number I am getting an error mentioning follows.

Error report -
SQL Error: ORA-00904: "DCS"."SUBSCRIBER"."SUB_NUMBER": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

I can call a column name in the same schema for the update query using (tableName.columnName). But how can I modify the above query to refer to columns in tables in different schemas?

Answer 1

You have to use a co-related subquery to achieve this -

UPDATE dcs.lte_pin_register
   SET pin = ''
 WHERE EXISTS (SELECT 1 
                 FROM dcs.subscriber
                WHERE dcs.lte_pin_register.subscriber_seqno = dcs.subscriber.subscriber_seqno
                  AND dcs.subscriber.sub_number = '?');