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 is a requirement, if code is duplicated, grouped by code Plus
--such as code=201402 's record, code: 201402_1, 201402_2
--1. You can use the analysis function to spell 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 rows have been selected.
--2. Look at the traditional way of writing.
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
6 rows have been selected.
sql> rollback;
-It looks like a temporary table needs to be built, then update and merge, but there's a way
--3.update and with combination
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
6 rows have been selected.
Usage Scenarios for Oracle Update+with
