update query result not reflecting in table

Question

I have two tables test1 and test2. What I need is, I would like to update one column in the table test2 with data from the table test1. My query is

 UPDATE test2 t2 
    SET t2.name = (SELECT t1.name 
                     FROM test1 t1 
                    WHERE t1.id = t2.mob)
  WHERE t2.mob IN (SELECT t1.id 
                     FROM test1 t1 
                    WHERE t1.id = t2.mob) 

It's showing 3 Rows updated , But It's not reflecting in my table. My reference. Is there any issue in my query. Or what should I do alternately.

Answer 1

It wold be easier to use merge statement:

/* test tables */

SQL> create table test1(id1, name1) as
  2    select level
  3         , dbms_random.string('l', 7)
  4    from dual
  5    connect by level <= 5;
Table created

SQL> create table test2(id1, name1) as
  2    select level
  3         , cast(null as varchar2(11))
  4    from dual
  5    connect by level <= 5;
Table created 

Tables' contents:

SQL> column name1 format a10;
SQL> select * from test1;

       ID1 NAME1
---------- ----------
         1 ouegwac
         2 bptytsz
         3 xwpnuqi
         4 jrbxeza
         5 hlckwvk

SQL> select * from test2;

       ID1 NAME1
---------- ----------
         1 NULL
         2 NULL
         3 NULL
         4 NULL
         5 NULL

Update test2.name1 column with data from test1.name1 column:

SQL> merge into test2 t
  2  using test1 q
  3     on (q.id1 = t.id1)
  4  when matched then
  5    update set t.name1 = q.name1
  6  ;

5 rows merged

SQL> select * from test2;

       ID1 NAME1
---------- ----------
         1 ouegwac
         2 bptytsz
         3 xwpnuqi
         4 jrbxeza
         5 hlckwvk

Answer 2

UPDATE 
(SELECT test2.name as t2, test1.name as t1
 FROM test2
 INNER JOIN test1
 ON test2.MOB= test1.ID

) t
SET t.t2= t.t1