SELECT * FROM TGoods WHERE FNo = '0011

'

SELECT * FROM TInventory WHERE FGoodsId = 5254077160902154974

SELECT * FROM TInventoryLog WHERE FGoodsId = 5254077160902154974
SELECT * FROM TInventory WHERE FGoodsId = 5254077160902154974

AND FCompId = 5581586899531004975

SELECT * FROM TBasicType WHERE FId = 7662466434053986928
SELECT * FROM TBasicType WHERE FKey = 'GoodsUnit'

SELECT * FROM TGoodsMultiUnit WHERE FGoodsId = 5254077160902154974

UPDATE TInventoryLog SET FUnitId = 7222064295725136969 WHERE FGoodsId = 5254077160902154974
UPDATE TInventoryLog SET FInStdQuantity = FInQuantity WHERE FGoodsId = 5254077160902154974
AND FCompId = 5581586899531004975

UPDATE TInventory SET FQuantity = 44 WHERE FGoodsId = 5254077160902154974
AND FCompId = 5581586899531004975



DECLARE @GoodsId BIGINT
DECLARE @NewUnitId BIGINT
DECLARE @OldUnitId BIGINT

DECLARE List CURSOR FOR SELECT FGoodsId,FNewUnitId,FOldUnitId FROM #Goods
OPEN LIST

FETCH List INTO @GoodsId,@NewUnitId,@OldUnitId

WHILE @@FETCH_STATUS = 0
BEGIN
 
UPDATE TGoods 
  SET FUnitId = @NewUnitId WHERE FId = @GoodsId AND FUnitId = @OldUnitId
  
UPDATE TGoodsMultiUnit 
SET FUnitId = @NewUnitId 
WHERE FGoodsId = @GoodsId
AND FUnitId = @OldUnitId

UPDATE TInventoryLog 
SET FUnitId = @NewUnitId 
WHERE FGoodsId = @GoodsId
AND FUnitId = @OldUnitId

UPDATE A
   SET A.FUnitId = @NewUnitId
  FROM TStockInDetail AS A
 WHERE A.FGoodsId = @GoodsId
   AND A.FUnitId = @OldUnitId
   
UPDATE A
   SET A.FUnitId = @NewUnitId
  FROM TStockOutDetail AS A
 WHERE A.FGoodsId = @GoodsId
   AND A.FUnitId = @OldUnitId
   
UPDATE A
   SET A.FUnitId = @NewUnitId
  FROM TTakeStockDetail AS A
 WHERE A.FGoodsId = @GoodsId
   AND A.FUnitId = @OldUnitId
   
UPDATE A
   SET A.FUnitId = @NewUnitId
  FROM TOrderGoods AS A
 WHERE A.FGoodsId = @GoodsId
   AND A.FUnitId = @OldUnitId
   

UPDATE A
   SET A.FUnitId = @NewUnitId
  FROM TConsumeGoods AS A
 WHERE A.FGoodsId = @GoodsId
   AND A.FUnitId = @OldUnitId
   
FETCH List INTO @GoodsId,@NewUnitId,@OldUnitId
END

SELECT object_name(id),* FROM syscolumns WHERE name like '%UnitId%'
AND id in(select id from sysobjects where type = 'u')

DROP TABLE
#Goods
GO
 CREATE TABLE #Goods
 (  
    FGoodsId BIGINT,
    FGoodsNo VARCHAR(50),
    FGoodsName VARCHAR(50),
    FOldUnitId BIGINT,
    FOldUnitName VARCHAR(20),
    FNewUnitId BIGINT,
    FNewUnitName VARCHAR(20)
 )  
 
 
 
 dbo.FNToDBC('００２７'),
 dbo.FNToDBC('００３０'),dbo.FNToDBC('００３２'),dbo.FNToDBC('００３５'),dbo.FNToDBC('００３９'),dbo.FNToDBC('００４４'),dbo.FNToDBC('００６０'),dbo.FNToDBC('０１１２'),dbo.FNToDBC('６６０１'),dbo.FNToDBC('８８６８'),dbo.FNToDBC('８８７９'),dbo.FNToDBC('８８８１')
 
 
 INSERT #Goods(FGoodsNo,FNewUnitName)
 SELECT dbo.FNToDBC('００１３'),'包'
 UNION ALL
 SELECT dbo.FNToDBC('００１４'),'卷'
  UNION ALL
 SELECT dbo.FNToDBC('００１９'),'卷'
 UNION ALL
 SELECT dbo.FNToDBC('７８３６'),'袋'
  UNION ALL
 SELECT dbo.FNToDBC('８８５５'),'盒'
UNION ALL
 SELECT dbo.FNToDBC('８８５６'),'盒'
 UNION ALL
 SELECT dbo.FNToDBC('８８５７'),'盒'
 UNION ALL
 SELECT dbo.FNToDBC('００４５'),'瓶'
 UNION ALL
 SELECT dbo.FNToDBC('８８８０'),'瓶'


 INSERT #Goods(FGoodsNo,FNewUnitName)
SELECT FNo,'支'
 FROM TGoods 
 WHERE FNo BETWEEN dbo.FNToDBC('７７１０') AND  dbo.FNToDBC('７８３５')

 INSERT #Goods(FGoodsNo,FNewUnitName)
SELECT FNo,'袋'
 FROM TGoods 
 WHERE FNo BETWEEN dbo.FNToDBC('７７０１') AND  dbo.FNToDBC('７７０５')
 
  INSERT #Goods(FGoodsNo,FNewUnitName)
SELECT FNo,'支'
 FROM TGoods 
 WHERE FNo IN(
  dbo.FNToDBC('００２７'),
 dbo.FNToDBC('００３０'),dbo.FNToDBC('００３２'),dbo.FNToDBC('００３５'),dbo.FNToDBC('００３９'),dbo.FNToDBC('００４４'),dbo.FNToDBC('００６０'),dbo.FNToDBC('０１１２'),dbo.FNToDBC('６６０１'),dbo.FNToDBC('８８６８'),dbo.FNToDBC('８８７９'),dbo.FNToDBC('８８８１'))
 
 


 
 

UPDATE A
   SET A.FGoodsId = B.FId,
       A.FGoodsName = B.FName,
       A.FOldUnitId = B.FUnitId,
       A.FOldUnitName = S.FName,
       A.FNewUnitId = S2.FId,
       A.FNewUnitName = S2.FName
  FROM #Goods AS A,TBasicType AS S,TBasicType AS S2,TGoods AS B
 WHERE A.FGoodsNo = B.FNo
   AND S.FId = B.FUnitId
   AND S2.FName = A.FNewUnitName
   
 
 SELECT  A.FGoodsnO AS 编号,A.FGoodsName AS 名称,A.FOldUnitName AS 原单位,A.FNewUnitName AS 新单位
  FROM #Goods AS A ORDER BY FGoodsNo ASC
 
 
 
 USE JINAN_GT
 
 SELECT * FROM TGoods WHERE FNo = '0011'
 
 SELECT * FROM TInventory WHERE FGoodsID = 5254077160902154974
 
 SELECT * FROM TInventoryLog WHERE FGoodsID = 5254077160902154974