oracle用 with update,Oracle update+with的使用场景

drop table test purge;

create table test(

id number,

code varchar(20),

name varchar(20)

);

insert into test values(1,'201401','aaa');

insert into test values(2,'201402','bbb');

insert into test values(3,'201402','ccc');

insert into test values(4,'201403','ddd');

insert into test values(5,'201403','eee');

insert into test values(6,'201403','fff');

commit;

--现在有这个一个需求，如果code有重复，根据code进行分组加上1,2,3，

--如code=201402的记录，code为：201402_1、201402_2

--1.可以用分析函数拼出code

SQL> select t.id,code||'_'||row_number() over(partition by code order by id) cc from test t;

ID CC

---------- -------------------------------------------------------------

1 201401_1

2 201402_1

3 201402_2

4 201403_1

5 201403_2

6 201403_3

已选择6行。

--2.用传统写法看行不行，发现不行

SQL> update test t set t.code=(select code||'_'||row_number()

over(partition by code order by id) code

from test t1 where t1.id=t.id);

已更新6行。

SQL> select * from test;

ID CODE                 NAME

---------- -------------------- --------------------

1 201401_1             aaa

2 201402_1             bbb

3 201402_1             ccc

4 201403_1             ddd

5 201403_1             eee

6 201403_1             fff

已选择6行。

SQL> rollback;

--看来需要建一个临时表，然后用update和merge,不过还有一种写法

--3.update和with组合 SQL> update test b set b.code=(     with t as     (select t.id,code||'_'||row_number() over(partition by code order by id) code         from test t)     select a.code from t a where a.ID=b.ID     ); 已更新6行。 SQL> select * from test;         ID CODE                 NAME ---------- -------------------- --------------------          1 201401_1             aaa          2 201402_1             bbb          3 201402_2             ccc          4 201403_1             ddd          5 201403_2             eee          6 201403_3             fff 已选择6行。