update问题:对所有记录中某个字段的值,取任一个记录,更新为1,其他为0

[lm517]

请用一个update语句完成.

[Zoezs]

用一条语句怎么完成啊 ？

[htl258_Tony]

随便一个字段？随便一个值？

[mugua604]

update a set a.col= (case when a.col ='' then 1 else 0 end) from tableName a

[csdyyr]

create table tb(col int)
insert tb
select 1 union all
select 2 union all
select 3
go

set rowcount 1
update tb set col=1
set rowcount 0

update tb set col=0 where col<>1

select * from tb

drop table tb
/*
col
-----------
1
0
0
*/

[Zoezs]

开始
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

[Zoezs]

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

[lm517]

非常感谢大家,我的表达有误,应该是:
对所有记录中某个字段的值,如果值都是0,则取任一个记录,更新为1,其他为0,请赐教.

[claro]

奇怪的需求。

[csdyyr]

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 
*/

[lm517]

to csdyyr:没有执行成功啊.

[lm517]

to csdyyr:没有成功啊

[-晴天]

--任意可以,但必须有一个标记列,否则不好处理更新哪一行.下面以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

*/

[lzhs]

借用一下上面的建表语句：

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)