游标的使用

SQL code 如何批量修改一列的值？把另一张表的某个字段对应的赋到这张表的某一字段中

 

update   table1   set   c1=(select   c2   from   table2   where   c1.main=c2.main);

--用游标
declare @str varchar(100) 
--定义游标
declare DZCursor CURSOR for SELECT test_str FROM test where test_str='xxx' 
--打开游标
open  DZCursor 
--从游标取记录
fetch next from DZCursor into @str 
--当有记录
while @@fetch_status=0 
begin 
insert into test (test_str) select @str 
--取下一条记录
fetch next from DZCursor into @str 
end 
--关闭游标
close DZCursor 
--删除游标引用
deallocate DZCursor 

例子：
declare @OrderNumber varchar(30)
declare @OrderTypeID varchar(30)

declare cur_name cursor for 
Select OrderNumber,OrderTypeID From orderheader where OrderNumber>'50000' Order By OrderNumber  

open cur_name

fetch next from cur_name  into @OrderNumber,@OrderTypeID
while(@@fetch_status = 0)

begin
    --insert into @table(tablename)
    --values(@cur_tab)
    PRINT  N'订单=' +@OrderNumber+N' 类型: '+@OrderTypeID
    fetch next from cur_name into  @OrderNumber,@OrderTypeID 
end
close cur_name
deallocate cur_name 

declare @ItemID nvarchar(50)
declare @IventoryQty nvarchar(50)
declare @JoinNumber nvarchar(50)

declare cur_IventoryQty cursor for 

SELECT
  A1.ItemID,A1.InvoiceNumber,
  '|'+Convert(nvarchar(50),B1.QtyOnHand)+'|'+Convert(nvarchar(50),B1.QtyCommitted)+'|'+Convert(nvarchar(50),B1.QtyOnOrder)+'|'+Convert(nvarchar(50),B1.QtyOnBackorder)
  as IventoryQty
  FROM InvoiceDetail A1 
  LEFT JOIN InventoryByWarehouse B1 ON A1.ItemID=B1.ItemID 
  WHERE  A1.InvoiceNumber=@InvoiceNumber
 
open cur_IventoryQty

fetch next from cur_IventoryQty  into  @ItemID,@JoinNumber,@IventoryQty
while(@@fetch_status = 0)

begin 

     UPDATE InvoiceDetail SET SerialNumber=@IventoryQty where ItemID=@ItemID and InvoiceNumber=@JoinNumber
     fetch next from cur_IventoryQty  into  @ItemID,@JoinNumber,@IventoryQty
end
close cur_IventoryQty
deallocate cur_IventoryQty