Friday, July 30, 2010

User Cursor to Set Update One Table Coloum Values To Another Table

In This Example replace Product Table TotalStockU1  values with StockRecord table StockU1 values.

DECLARE @name decimal(13, 4) 
DECLARE @product varchar(24)

DECLARE db_cursor CURSOR FOR
(SELECT RD.StockRecord.StockU1,RD.StockRecord.ProductCode
FROM RD.StockRecord INNER JOIN RD.Product ON
RD.StockRecord.ProductCode = RD.Product.ProductCode and RD.StockRecord.BusinessUnit=RD.Product.BusinessUnit
WHERE RD.StockRecord.ProductCode = RD.Product.ProductCode and RD.StockRecord.BusinessUnit='TEST')

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name,@product 

WHILE @@FETCH_STATUS = 0 
BEGIN 
      UPDATE RD.Product     
        SET TotalStockU1 =@name        
WHERE
       
         RD.Product.BusinessUnit='TEST' and RD.Product.ProductCode=@product;
       FETCH NEXT FROM db_cursor INTO @name,@product   
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

No comments:

Post a Comment