这个如何循环

[lenovo123]

select * from split('aa;bb;cc;dd',';')
StringToTable是分隔函数

产生的结果如下:
table1
aa
bb
cc
dd

想根据这个表写四个sql
如:
update t1 set 字段=aa
update t1 set 字段1=aa
update t1 set 字段2=aa
update t1 set 字段3=aa
该如何写啊?

[lenovo123]

写错了, 应该是这样
update t1 set 字段=aa
update t1 set 字段1=bb
update t1 set 字段2=cc
update t1 set 字段3=dd

[Garnett_KG]

select IDENTITY(INT,1,1) as Row ,* INTO #Tmp from  split('aa;bb;cc;dd',';') 

UPDATE t1
SET    字段=(SELECT TOP 1 val FROM #Tmp WHERE Row=1),
       字段1=(SELECT TOP 1 val FROM #Tmp WHERE Row=2),
       字段2=(SELECT TOP 1 val FROM #Tmp WHERE Row=3),
       字段3=(SELECT TOP 1 val FROM #Tmp WHERE Row=4)


DROP TABLE #Tmp

[-狙击手-]

产生的结果如下:
table1
aa
bb
cc
dd

－－－

------------------------------------
-- Author:  happyflsytone  
-- Date:2008-10-22 22:40:26
------------------------------------

-- Test Data: table1
IF OBJECT_ID('table1') IS NOT NULL 
    DROP TABLE table1
Go
CREATE TABLE table1(aa NVARCHAR(2))
Go
INSERT INTO table1
 SELECT 'aa' UNION ALL
 SELECT 'bb' UNION ALL
 SELECT 'cc' UNION ALL
 SELECT 'dd' 
GO
--Start
declare @s varchar(8000)

select @s = isnull(@s+';','') + ' update t1 set 字段='''+aa+''' '
from table1 
print @s

--Result:
/*

update t1 set 字段='aa' ; update t1 set 字段='bb' ; update t1 set 字段='cc' ; update t1 set 字段='dd' 

*/
--End 

[-狙击手-]

晕，
是字段N

参KG

[mathsfan]

大概思路应该定义变量 'aa;bb;cc;dd'，然后根据分割，再赋值.

[wzy_love_sly]

create table t1(字段0 varchar(100),字段1 varchar(100),字段2 varchar(100),字段3 varchar(100))
go
declare @sql varchar(8000),@char varchar(1)
set @char='aa'
select @sql=isnull(@sql,'')+'update t1 set '+name+'='''+@char+''';'
from syscolumns where id=object_id('t1')
select @sql
exec(@sql)

update t1 set 字段0='a';
update t1 set 字段1='a';
update t1 set 字段2='a';
update t1 set 字段3='a';

[Andy-W]

Declare @string nvarchar(4000)
Declare @x xml
Declare @sql nvarchar(4000)

Set @string=N'aa;bb;cc;dd'
Set @x=Convert(xml,'<r>'+Replace(@string,';','</r><r>')+'</r>')

;With t As
(Select id=1,t.v.value('.','nvarchar(50)') As x From @x.nodes('r') t(v))
Select @sql=Isnull(@sql+Char(13)+Char(10),'')+'update t1 Set 字段'+Replace(id-1,0,'')+'='+x
	From (Select id=Row_number() Over(order By id),x From t) a

Print @sql

/*
update t1 Set 字段=aa
update t1 Set 字段1=bb
update t1 Set 字段2=cc
update t1 Set 字段3=dd

*/