In reality, databases have a lot of duplicate data. The following is an example:
There is a database with two fields: pinyin (pinyin of person name) and Mima (password)
There are many repeated human names, not to mention pinyin. My goal is:
1. Add dynamic numbers after the second duplicate pinyin, such as zhangsan, zhangsan2, and zhangsan3.
2. Change the password after zhangsan2 to 654321.
Practices: (The following are the low efficiency practices of SQL Server. We will introduce the high efficiency practices of Oracle later)
1. Define a scroll and dynamically updatable cursor
2. Write all the table variables that have repeated pinyin characters in the table for comparison with the full table one by one.
Declare Chong cursor scroll dynamic
Select pinyin, Mima from winmail for update
-- Chong pointer. It is important that scroll indicates that the cursor can be rolled cyclically, and dynamic indicates timely dynamic updates.
Open Chong
Declare @ disjing table (Xid int identity (1, 1), Ren varchar (20 ))
Insert @ disjing (ren) Select pinyin from winmail group by pinyin having count (pinyin)> 1
-- Find the duplicate persons from all
Declare @ current int
Declare @ allcount int
Declare @ currren varchar (20) -- use someone to compare with everyone
Set @ current = 1
Set @ allcount = (select count (*) from @ disjing)
Declare @ cursorren varchar (20) -- The current person indicated by the pointer
Declare @ Mima varchar (20)
Declare @ currshu int -- after entering the loop, this determines the number of duplicates.
While @ current <= @ allcount
Begin
Fetch first from Chong
Set @ currshu = 1
Set @ currren = (select Ren from @ disjing where Xid = @ current)
While @ fetch_status = 0
Begin
Fetch next from Chong into @ cursorren, @ Mima
If @ currren = @ cursorren
Begin
If @ currshu> 1
Update winmail set pinyin = pinyin + Cast (@ currshu as varchar (5), Mima = '000000' where current of Chong
Set @ currshu = @ currshu + 1
End
End
Set @ current = @ current + 1
End
Close Chong
Deallocate Chong
The following is an efficient way: -- The following example is: Table test, with only one field name
Declare
NAM varchar2 (12 );
I int;
Begin
For ABC in (Select name from test group by name having count (*)> 1) loop
Begin
NAM: = ABC. Name;
I: = 1;
-- The unique "rowid" in Oracle is extracted to keep it unique.
For def in (select rowid from test where name = NAM) loop
Begin
If I> 1 then
Update Test Set Name = Nam | to_char (I) Where rowid = def. rowid;
End if;
I: = I + 1;
End;
End loop;
End;
End loop;
End;