update a set a.col= (case when a.col ='' then 1 else 0 end) from tableName a
create table tb(col int) insert tb select 1 union all select 2 union all select 3 goset rowcount 1 update tb set col=1 set rowcount 0update tb set col=0 where col<>1select * from tbdrop table tb /* col ----------- 1 0 0 */
开始 select * from tb a b c d 1 2 3 4 3 4 5 6 1 3 5 7 1.2 1.4 2.2 3.5 然后 update A set a=(case when a=1 then 5 else 10 end) from tb A a b c d 5 2 3 4 10 4 5 6 5 3 5 7 10 1.4 2.2 3.5
update A set a=( case when a=(select top 1 a from tb order by newid()) then 1 else 0 end ) from tb A 这样就满足你的要求了。 a b c d 1 2 3 4 0 4 5 6 1 3 5 7 0 1.4 2.2 3.5
create table tb(col int) insert tb select 0 union all select 0 union all select 0 go if (select sum(col) from tb)=0 begin set rowcount 1 update tb set col=1 set rowcount 0 update tb set col=0 where col <>1 end select * from tb drop table tb /* col ----------- 1 0 0 */
--任意可以,但必须有一个标记列,否则不好处理更新哪一行.下面以id列作为标记列,你可以用其他列,但必须唯一, create table tb(id int,col1 int,col2 int) insert into tb select 1,1,0 insert into tb select 2,4,0 insert into tb select 3,6,0 insert into tb select 4,7,1 insert into tb select 5,4,0 insert into tb select 6,6,0 insert into tb select 7,4,0 go update tb set col2=1 from tb a where id=(select top 1 id from tb where col2=0 order by newid()) select * from tb go drop table tb /* 第一次 id col1 col2 ----------- ----------- ----------- 1 1 0 2 4 0 3 6 0 4 7 1 5 4 1 6 6 0 7 4 0 第二次 id col1 col2 ----------- ----------- ----------- 1 1 0 2 4 1 3 6 0 4 7 1 5 4 0 6 6 0 7 4 0 ... id col1 col2 ----------- ----------- ----------- 1 1 0 2 4 0 3 6 0 4 7 1 5 4 0 6 6 1 7 4 0*/
借用一下上面的建表语句: create table tb(id int,col1 int,col2 int) insert into tb select 1,1,0 insert into tb select 2,4,0 insert into tb select 3,6,0 insert into tb select 4,7,0 insert into tb select 5,4,0 insert into tb select 6,6,0 insert into tb select 7,4,0 go SELECT * FROM tb GO update t set col2=CASE WHEN col2 = 0 THEN 1 ELSE col2 END from (SELECT TOP 1 * FROM tb order by col2 DESC) t GO select * from tb go drop table tb
我认为可以的用以下语句来实现if (select count(1) from table)=(select count(1) from table where fn=0) update table set fn=1 from table where id=(select top 1 id from Table)
update a set a.col= (case when a.col ='' then 1 else 0 end) from tableName a
insert tb
select 1 union all
select 2 union all
select 3
goset rowcount 1
update tb set col=1
set rowcount 0update tb set col=0 where col<>1select * from tbdrop table tb
/*
col
-----------
1
0
0
*/
开始
select * from tb
a b c d
1 2 3 4
3 4 5 6
1 3 5 7
1.2 1.4 2.2 3.5
然后
update A set a=(case when a=1 then 5 else 10 end) from tb A
a b c d
5 2 3 4
10 4 5 6
5 3 5 7
10 1.4 2.2 3.5
update A set a=( case when a=(select top 1 a from tb order by newid()) then 1 else 0 end )
from tb A
这样就满足你的要求了。
a b c d
1 2 3 4
0 4 5 6
1 3 5 7
0 1.4 2.2 3.5
insert tb
select 0 union all
select 0 union all
select 0
go if (select sum(col) from tb)=0
begin
set rowcount 1
update tb set col=1
set rowcount 0 update tb set col=0 where col <>1
end
select * from tb drop table tb
/*
col
-----------
1
0
0
*/
create table tb(id int,col1 int,col2 int)
insert into tb select 1,1,0
insert into tb select 2,4,0
insert into tb select 3,6,0
insert into tb select 4,7,1
insert into tb select 5,4,0
insert into tb select 6,6,0
insert into tb select 7,4,0
go
update tb set col2=1 from tb a where id=(select top 1 id from tb where col2=0 order by newid())
select * from tb
go
drop table tb
/*
第一次
id col1 col2
----------- ----------- -----------
1 1 0
2 4 0
3 6 0
4 7 1
5 4 1
6 6 0
7 4 0
第二次
id col1 col2
----------- ----------- -----------
1 1 0
2 4 1
3 6 0
4 7 1
5 4 0
6 6 0
7 4 0
...
id col1 col2
----------- ----------- -----------
1 1 0
2 4 0
3 6 0
4 7 1
5 4 0
6 6 1
7 4 0*/
create table tb(id int,col1 int,col2 int)
insert into tb select 1,1,0
insert into tb select 2,4,0
insert into tb select 3,6,0
insert into tb select 4,7,0
insert into tb select 5,4,0
insert into tb select 6,6,0
insert into tb select 7,4,0
go
SELECT * FROM tb
GO
update t
set col2=CASE WHEN col2 = 0 THEN 1 ELSE col2 END
from (SELECT TOP 1 * FROM tb order by col2 DESC) t
GO
select * from tb
go
drop table tb
update table set fn=1 from table where id=(select top 1 id from Table)