Use of identity

User Requirements:

You have a table named test.

 

Ip	Name
127.3.2.0	Ken
127.3.2.0	King
127.3.2.0	Kent

 

You want to use SQL statements to update the ip column values of the existing rows in the test table, and so on, in sequence by 127.0.0.1 and 127.0.0.2, that is, the last bit increments sequentially.

Problem Analysis:

First, perform a test on the temporary table, test the code, and view the output result.

1 -- Generate Test Data
2 create table tab (id varchar (5), tt varchar (5 ))
3
4 insert into tab
5 select 'w', 'A'
6 union select 'R', 'B'
7 union select 'x', 'C'
8 union select 'A', 'D'
9 union select 'F', 'E'
10
11 select * from tab
12 /**//*
13 id tt
14 -------------------
15 a d
16 f e
17 r B
18 w
19 x c
20 */
21
22 -- UPDATE
23 alter table tab ADD index_c int IDENTITY (1, 1)
24 update tab set id = index_c
25 alter table tab drop column index_c
26
27
28 select * from tab
29 /**//*
30 id tt
31 -------------------
32 1 d
33 2 e
34 3 B
35 4
36 5 c
37 */
38
39 -- delete test data
40 drop table tab
41

 

Solution:

For more information, see the example above.

Step 1: alter table test ADD index_c int IDENTITY (1, 1)
Step 2:
UPDATE test SET ip = '2014. 22. 8. '+ cast (index_c as varchar)
Alter table test drop column index_c
Select * from test
The result is as follows:

 

Ip	Name
127.0.0.1	Ken
127.0.0.2	King
127.0.0.3	Kent