sql - 更新查询结果未反射(reflect)在表中

Question

我有两张 table test1和 test2 .我需要的是，我想更新表中的一列test2数据来自表 test1 .我的查询是

 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) 

它正在显示 3 Rows updated ，但它没有反射(reflect)在我的表中。我的 reference .我的查询中是否有任何问题。或者我应该交替做什么。

Answer 1

使用起来会更方便merge陈述:

/* 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 

表格内容:

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

更新 test2.name1包含来自 test1.name1 的数据的列柱子:

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