oracle update 操作一二

os: centos 7.6
db: oracle 19.3

准备数据

创建表

-- drop table tmp_t0;
create table tmp_t0
(
  id   VARCHAR2(100),
  name VARCHAR2(100),
  memo VARCHAR2(100)
)
;

-- drop table tmp_t1;
create table tmp_t1
(
  id   VARCHAR2(100),
  name VARCHAR2(100)
)
;

插入数据

insert into tmp_t0 values('1','a','a');
insert into tmp_t0 values('2','b','b');
insert into tmp_t0 values('3','c','c');

insert into tmp_t1 values('1','a');
insert into tmp_t1 values('2','b');
insert into tmp_t1 values('4','d');

commit;

SQL> set lines 200;
set pages 200;

col id format a5;
col name format a5;
col memo format a5;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

SQL> select * from tmp_t1;

ID    NAME
----- -----
1     a
2     b
4     d

update style 1

最简单的update

SQL> update tmp_t0 t0
   set t0.memo = t0.name || t0.name
 where 1=1
   and t0.id='1'
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     aa
2     b     b
3     c     c

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 2

有关联的update

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 
                    where t0.id = t1.id ) 
 where 1=1
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c

有个现象,id=3 的 memo 列被更新成了NULL,本来只希望更新关联到的值，结果连没有关联到的也被更新为NULL了。

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

在 style 2 上添加一个条件

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 
                    where t0.id = t1.id and t0.id='1' ) 
 where 1=1
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b
3     c

有意思吧，其实 update 的行数是通过最下面那个where 限制的

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 3

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                    from tmp_t1 t1 where t0.id = t1.id ) 
 where 1=1
   and exists ( select t1.name ||'_'|| t1.name 
                  from tmp_t1 t1 where t0.id = t1.id )
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c

或者使用 in

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 where t0.id = t1.id ) 
 where 1=1
   and t0.id in ( select t1.id 
                    from tmp_t1 t1 where t0.id = t1.id )
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c

这个结果是预期的

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 4

感觉现在使用 merge 应该是最简单

merge into tmp_t0 t0 using (
   select *
     from tmp_t1
) t1
on (
   t0.id = t1.id
)
when matched then
  update set t0.memo = t1.name ||'_'|| t1.name
/*  
when not matched then
  insert (t0.id,t0.name,t0.memo)
  values (t1.id,t1.name,'merge')
*/
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c
  

参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F