Oracle with AS and update usage __oracle

Prepare data:

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

--now there's a demand, if code is duplicated, grouped by code, plus 1,2,3,
--such as code=201402 's record, code is: 201402_1, 201402_2
--1. You can spell code with analytic functions

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

--2. Use the traditional writing to see if you can.
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 rows have been updated.
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

- -It seems like you need to build a temporary table and then use Update and merge, but there's also a way of writing--3.update and with combinations
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 rows have been updated.

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