Search code examples
sqlselectsql-updatedatabase-link

UPDATE table from another TABLE using database link

I need to add values to username field in table1 from table2 in different connection.

What I have so far is:

UPDATE conn1.table1 
SET table1.USERNAME = 
            (
              SELECT MSUSERNAME  
              FROM conn2.table2@database_link,
                   conn1.table1 
              WHERE conn2.table2.ID = conn1.table1.USER_ID
            )
WHERE conn2.table2.ID = conn1.table1.USER_ID;

The inner SELECT returns what I want but when I add the rest of the code it returns:

Error at Command Line:9 Column:6
Error report:
SQL Error: ORA-00904: "PERSN_ALL"."WWID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I don't know how to solve this.

Anyone?

Solution 
  • UPDATE conn1.table1
SET table1.USERNAME = 
            (
              SELECT MSUSERNAME  
              FROM conn2.table2@database_link
              WHERE conn2.table2.ID = conn1.table1.USER_ID
            )
;