Oracle_dml

You can join the two table before the update. So the query could be:

UPDATE (SELECT a.CHANGE_NAME,UPDATE_NAME
          FROM TABLE1 a
          JOIN TABLE2 b ON a.NAME = b.UPDATE_NAME)
   SET UPDATE_NAME = CHANGE_NAME

Select allOpen in new window

@Antonio La Gamma: 'BAPUN' will be NULL with your statement :-(

@Alexander, thanks, I didn't see that exception in the column :)

It should works now using simple query without merge (patched but not perfect):

UPDATE (SELECT a.CHANGE_NAME,UPDATE_NAME
          FROM TABLE1 a
          JOIN TABLE2 b ON a.NAME = b.UPDATE_NAME 
         WHERE CHANGE_NAME != NULL)
SET UPDATE_NAME = CHANGE_NAME

Select allOpen in new window

@Antonio La Gamma: no problem ;-)
But you should avoid such update statements (update (select....) set...) for several reasons (e.g. you don't do updates on inline views). On top, in this case there is even a join within the statement. Just use what Oracle (ANSI SQL) gives you out of the box for these situations! Here it's the MERGE.

@Alexander thank you for the explanation but the join has to be done also in the merge posted by you. It is not explicit mentioned but using the Where clause to put the Join columns, the result is exactly the same: an Inner Join.
The result of two statement is no so distant as explained in various oracle posts. If there is a constraint between the two columns (eg Foreign Key), the UPDATE is still preferable.

In simple cases (like this one) IMHO I prefer the Update because is more "debuggable"   and readable
Off course, the oob solution is preferable in most of cases but is difficult to maintain it over the time in case of any changes of the structure

The fact is that all of the queries posted result in errors.
Alexander's has issues with column names.

Sorry, but NOPE: I just tested it on our Oracle DEV database running 11.2 and guess what: no errors! Considering the join I used, it's just a workaround you need to perform in order to get the name columns updated. These columns cannot be updated with a merge if you say ... on (dest.name = src.name) !!! So you need to use that pseudo join to make the match thru rowid column!

You're one of my fav experts here on EE, but on this one you kinda failed, sorry!

Errors when I run it:

SQL> create table table1 (name varchar2(30), change_name varchar2(30));

Table created.

SQL> insert into table1 values ('AVINASH',    'ABINASH');

1 row created.

SQL> insert into table1 values ('SAHU',          'SAHOO');

1 row created.

SQL> insert into table1 values ('ALIBHA',       'ALIVA');

1 row created.

SQL> insert into table1 values ('BAPUN',       NULL );

1 row created.

SQL> insert into table1 values ('RAZA',          'RAJA');

1 row created.

SQL> insert into table1 values ('BAPU',         'BABU');

1 row created.

SQL> create table table2 (update_name varchar2(30) primary key);

Table created.

SQL> insert into table2 values ('AVINASH');

1 row created.

SQL> insert into table2 values ('SAHU');

1 row created.

SQL> insert into table2 values ('ALIBHA');

1 row created.

SQL> insert into table2 values ('BAPUN');

1 row created.

SQL> insert into table2 values ('RAZA');

1 row created.

SQL> insert into table2 values ('BAPU');

1 row created.

SQL> commit;

Commit complete.

SQL> merge into table2 dest
  2  using (select t2.rowid rid,
  3                t1.*
  4           from table2 t2,
  5                table1 t1
  6          where t2.name(+) = t1.name) src
  7  on (dest.rowid = src.rid)
  8  when matched then
  9    update
 10       set dest.name = coalesce(src.change_name, src.name);
        where t2.name(+) = t1.name) src
              *
ERROR at line 6:
ORA-00904: "T2"."NAME": invalid identifier


SQL> spool off

Select allOpen in new window

@Johnsone If you create the tables using Primary Keys the error code is not triggered. Have a nice day

I had

create table table2 as select name from table1;

Select allOpen in new window

so just rename column ;-)

So, after altering the database design and adding primary keys where they don't exist.  Which I would contend invalidates the answer to start with, because we really shouldn't be asking people to change their design when they asked for a query to fix an issue.  This is what I see:

SQL> select * from table1;

NAME                           CHANGE_NAME
------------------------------ ------------------------------
AVINASH                        ABINASH
SAHU                           SAHOO
ALIBHA                         ALIVA
BAPUN
RAZA                           RAJA
BAPU                           BABU

6 rows selected.

SQL> select * from table2;

UPDATE_NAME
------------------------------
ALIBHA
AVINASH
BAPU
BAPUN
RAZA
SAHU

6 rows selected.

SQL> UPDATE (SELECT a.CHANGE_NAME,UPDATE_NAME
  2            FROM TABLE1 a
  3            JOIN TABLE2 b ON a.NAME = b.UPDATE_NAME
  4           WHERE CHANGE_NAME != NULL)
  5  SET UPDATE_NAME = CHANGE_NAME;

0 rows updated.

Select allOpen in new window

"There's more than one way to skin a cat." ;-)