标识列(identity)的妙用
用户需求:
用户有这样一个表test
Ip |
Name |
127.3.2.0 |
Ken |
127.3.2.0 |
King |
127.3.2.0 |
Kent |
希望通过SQL语句实现更新表test中现有行的ip列值,依次按照127.0.0.1、127.0.0.2依次类推,即最后一位依次递增。
问题分析:
我们首先通过临时表做个测试,测试一下代码并查看输出结果
1
--生成测试数据
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 a
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 a
36
5 c
37
*/
38
39
--删除测试数据
40
drop table tab
41![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
解决办法:
参考上例我们处理如下
Step1: ALTER TABLE test ADD index_c int IDENTITY(1,1)
Step2:
UPDATE test SET ip = '172.22.8.' + cast( index_c as varchar)
ALTER TABLE test DROP COLUMN index_c
select * from test
执行后结果如下:
Ip |
Name |
127.0.0.1 |
Ken |
127.0.0.2 |
King |
127.0.0.3 |
Kent |
思想指导实践,实践加以应用,应用创造价值