update tb4 set b=10 where b=(selet top 1 b from tb4 where b not in (select top 3 b from tb4))
create table tb4(a int, b int, c int) select * from tb4 --显示: a b c 1 1 2 3 2 4 5 6 3 7 8 9 4 7 8 9 这两行有什么区别 3 跟4 两行 你表只有三个字段? 重复的话,随便改一行不行?
alter table tb4 add sid int identity(1,1) update tb4 set b=10 where sid=4 alter table tb4 drop column sid
if object_id('tb1') is not null drop table tb1 go create table tb1(a int, b int, c int) insert tb1 select 1, 2, 3 union all select 4, 5, 6 union all select 7, 8, 9 union all select 7, 8, 9 if object_id('tempdb..#temp') is not null drop table #temp select identity(int,1,1) ID,* into #temp from tb1 update #temp set b=10 where id=4 truncate table tb1 insert tb1(a,b,c) select a,b,c from #temp select * from tb1 drop table #temp(4 行受影响)(4 行受影响)(1 行受影响)(4 行受影响) a b c ----------- ----------- ----------- 1 2 3 4 5 6 7 8 9 7 10 9(4 行受影响)
Create table tb4(a int, b int, c int) insert into tb4 select 1,2,3 Union all select 4,5,6 Union all select 7,8,9 Union all select 7,8,9 select * from tb4alter table tb4 add sid int identity(1,1) select * from tb4 update tb4 set b=10 where sid=4 alter table tb4 drop column sidselect * from tb4 drop table tb4
列名 'sid' 无效。
5楼的语句可以显示上面的结果。LZ能否把原始的TABLE贴出来看看。
if object_id('tb4') is not null drop table tb4 if object_id('c1') is not null drop table c1 go create table tb4 ( a int, b int, c int ) insert into tb4 select 1,2,3 union all select 4,5,6 union all select 7,8,9 union all select 7,8,9 go select * ,identity(int,1,1) d INTO c1 from tb4 update c1 set b=10 where d=4 select a,b,c from c1
update tb4 set b=10 where b=(selet top 1 b from tb4 where b not in (select top 3 b from tb4))
select * from tb4
--显示:
a b c
1 1 2 3
2 4 5 6
3 7 8 9
4 7 8 9
这两行有什么区别
3 跟4 两行
你表只有三个字段?
重复的话,随便改一行不行?
alter table tb4 add sid int identity(1,1)
update tb4 set b=10 where sid=4
alter table tb4 drop column sid
drop table tb1
go
create table tb1(a int, b int, c int)
insert tb1 select
1, 2, 3 union all select
4, 5, 6 union all select
7, 8, 9 union all select
7, 8, 9
if object_id('tempdb..#temp') is not null
drop table #temp
select identity(int,1,1) ID,* into #temp from tb1
update #temp set b=10 where id=4
truncate table tb1
insert tb1(a,b,c) select a,b,c from #temp
select * from tb1
drop table #temp(4 行受影响)(4 行受影响)(1 行受影响)(4 行受影响)
a b c
----------- ----------- -----------
1 2 3
4 5 6
7 8 9
7 10 9(4 行受影响)
Create table tb4(a int, b int, c int)
insert into tb4 select 1,2,3 Union all select 4,5,6 Union all select 7,8,9 Union all select 7,8,9
select * from tb4alter table tb4 add sid int identity(1,1)
select * from tb4
update tb4 set b=10 where sid=4
alter table tb4 drop column sidselect * from tb4
drop table tb4
列名 'sid' 无效。
if object_id('tb4') is not null
drop table tb4
if object_id('c1') is not null
drop table c1
go create table tb4
( a int,
b int,
c int
)
insert into tb4 select 1,2,3
union all select 4,5,6
union all select 7,8,9
union all select 7,8,9
go select * ,identity(int,1,1) d INTO c1 from tb4
update c1 set b=10 where d=4
select a,b,c from c1