Use Cases of Oracle update + with, oracleupdate
Drop table test purge;
Create table test (
Id number,
Code varchar (20 ),
Name varchar (20)
);
Insert into test values (1, '20170101', 'aaa ');
Insert into test values (2, '20170101', 'bbb ');
Insert into test values (3, '20170101', 'ccc ');
Insert into test values (4, '20170101', 'ddd ');
Insert into test values (5, '20170101', 'eee ');
Insert into test values (6, '20170101', 'fff ');
Commit;
-- Now we have this requirement. If the code already exists, add 1, 2, 3 to the group based on the code,
-- For example, for a record with code = 201402, the code is: 201402_1 and 201402_2.
-- 1. You can use the analysis function to spell out the 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
You have selected 6 rows.
-- 2. I can't see it in traditional writing, but I can't find it.
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 );
Six rows have been updated.
SQL> select * from test;
ID CODE NAME
--------------------------------------------------
1 201401_1 aaa
2 201402_1 bbb
3 ccc 402_1 ccc
4 201403_1 ddd
5 201403_1 eee
6 201403_1 fff
You have selected 6 rows.
SQL> rollback;
-- It seems that you need to create a temporary table and use update and merge, but there is another way to write
-- 3. Combination of update and
SQL> update test B set B. code = (
With t
(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
);
Six rows have been updated.
SQL> select * from test;
ID CODE NAME
--------------------------------------------------
1 201401_1 aaa
2 201402_1 bbb
3 ccc 402_2 ccc
4 201403_1 ddd
5 201403_2 eee
6 201403_3 fff
You have selected 6 rows.