我现在有一个表TABLE如下:ZY SH BJ HZ
001 5 0 3
002 4 1 0
003 10 0 0
004 202 101 95
005 0 8 20我现在要把SH BJ HZ字段中的为"0"的值全部替换成NULL ,请问要怎么写
001 5 0 3
002 4 1 0
003 10 0 0
004 202 101 95
005 0 8 20我现在要把SH BJ HZ字段中的为"0"的值全部替换成NULL ,请问要怎么写
set
SH=(case SH when 0 then null else SH end),
BJ=(case BJ when 0 then null else BJ end),
HZ=(case HZ when 0 then null else HZ end)
insert into ta select '001',5,0,3
union all select '002',4,1,0
union all select '003',10,0,0
union all select '004',202,101,95
union all select '005',0,8,20
declare @SQL nvarchar(1000)
declare @name nvarchar(10)
set @SQL=''
declare my_cursor cursor for
select distinct syscolumns.[name] from syscolumns
inner join sysobjects on sysobjects.id= syscolumns.id
and sysobjects.[name]='ta'
and syscolumns.typestat=0open my_cursor
fetch next from my_cursor into @name
while (@@fetch_status=0)
begin
set @sql=@sql+','+@name+'=(case '+@name+' when 0 then null else '+@name+' end)'
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursor
set @SQL=substring(@SQL,2,len(@SQL)-1)
select @SQL = 'update ta set '+@SQL
--select @SQL
exec(@SQL)
drop table ta