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.

enter image description here

score 2 · Answer 1 · answered Dec 26 '13 at 07:00

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

score 1 · Answer 2 · answered Dec 26 '13 at 06:51

1

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

answered Dec 26 '13 at 06:51

Mudassir Hasan

28,083
20
99
133

score 0 · Accepted Answer · answered Dec 26 '13 at 07:48

The WHERE part in your query is absolutely unnecessary because it always evaluates to TRUE. So the correct one to update all rows in t2 is:

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

Also in PL/SQL Developer transactions are not commited automatically by default. You have to manually commit it by pressing that green arrow on the panel.

enter image description here

update query result not reflecting in table

3 Answers3