SQL批量更新

SET NOCOUNT ON


IF EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAccounts') AND type ='U' )
   DROP TABLE #tempAccounts

SELECT FID, FAccount,FCompanyID, FTotalGeneration,FRegisterDate ,FStatus ,FLastLogonDate ,FLastLogonIP
INTO #tempAccounts
FROM dbo.TAccounts WITH(NOLOCK)
WHERE FCompanyID= 36052


--临时表变量
DECLARE @t_Deposit AS TABLE
(
   --会员
   FAccountID INT ,
   FAccount VARCHAR( 38),

   --最后操作人   
   FUpdateUserId INT,
   FUpdateUserName VARCHAR( 38),

   --所属总代
   FTotalGeneration INT,
   FTotalGenerationName VARCHAR( 38)
)
INSERT INTO @t_Deposit (FAccountID, FAccount, FUpdateUserId,FUpdateUserName ,FTotalGeneration ,FTotalGenerationName )
SELECT
   a.FAccountID , b.FAccount ,
   a.FUpdateUserId , ISNULL(c.FAccount, ''),
   b.FTotalGeneration ,''
FROM dbo.TDeposit AS a
--得到最后操作人名称
LEFT JOIN #tempAccounts AS b ON b.FID = a.FAccountID
LEFT JOIN #tempAccounts AS c ON c.FID = a.FUpdateUserId
WHERE a.FCompanyID= 36052
AND a.FStatus= 1 --状态[0=管理员未处理,1=同意,2= 拒绝]



--返回表1
SELECT * FROM  @t_Deposit
WHERE FTotalGeneration > 0


--更新所属总代信息
UPDATE t1
SET FTotalGenerationName= b.FAccount 
FROM @t_Deposit t1
INNER JOIN #tempAccounts b ON t1.FTotalGeneration = b.FID


--返回表2
SELECT * FROM  @t_Deposit
WHERE FTotalGeneration > 0



--清理临时表
IF EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAccounts') AND type ='U' )
BEGIN
   TRUNCATE TABLE #tempAccounts
   DROP TABLE #tempAccounts
END

SET NOCOUNT OFF

 

 

 

主要语句：

--批量更新
UPDATE t1
SET FTotalGenerationName = b.FAccount 
FROM @t_Deposit t1
INNER JOIN #tempAccounts b ON t1 .FTotalGeneration = b.FID

 

 

--批量删除
DELETE t1
FROM @t_Deposit t1
INNER JOIN #tempAccounts b ON t1.FTotalGeneration = b.FID