SQL 存储过程 示例（UPDATE A SET... FROM（SELECT...）WHERE B. =A .

USE [TJKJ_Common_System_TIEJUN]
GO

/****** Object:  StoredProcedure [dbo].[PRO_CUS_BALANCE_FREEZE]    Script Date: 07/21/2016 16:27:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        
-- Create date: 2016-07-11 11:00:44
-- Description:    二级分销系统：冻结余额=》可提现余额，
-- Description:    条件: DRP_ORDER_CUS
-- Description: 修改表 DRP_ORDER_CUS 状态IS_TRANSFER='1'
-- Description: 修改表 DRP_CUS_BALANCE 的BALANCE,BALANCE_FREEZE
-- =============================================
CREATE PROCEDURE [dbo].[PRO_CUS_BALANCE_FREEZE]
AS
BEGIN       
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
      
       BEGIN TRANSACTION
            BEGIN TRY
            
                --修改 客户余额表DRP_CUS_BALANCE
                UPDATE DRP_CUS_BALANCE
                   SET BALANCE=BALANCE+B.CUS_BALANCE_SUM,
                       BALANCE_FREEZE=BALANCE_FREEZE-B.CUS_BALANCE_SUM
                  FROM (
                               SELECT CUS_CODE,
                                   SUM(PRICE) AS CUS_BALANCE_SUM
                              FROM DRP_ORDER_CUS
                             WHERE CONVERT(VARCHAR(10),DATEADD(day,FREEZE_DAY,INSERTTIME),102)<=CONVERT(VARCHAR(10),GETDATE(),102)
                               AND IS_TRANSFER='0'
                               AND CHECK_STATUS='1'
                               AND S_FlAG='0'
                          GROUP BY CUS_CODE
                        )AS B 
                 WHERE DRP_CUS_BALANCE.CUS_CODE=B.CUS_CODE
                 
                 --保存客户余额解冻记录
             INSERT INTO DRP_CUS_BALANCE_TRANSFER_RECORD 
                         (SI_CODE ,INSERTTIME,REMARK )    
                  SELECT DISTINCT(SI_CODE),GETDATE(),'数据库定时更新'
                    FROM DRP_ORDER_CUS
                   WHERE CONVERT(VARCHAR(10),DATEADD(day,FREEZE_DAY,INSERTTIME),102)<=CONVERT(VARCHAR(10),GETDATE(),102)
                     AND IS_TRANSFER='0'
                     AND CHECK_STATUS='1'
                     AND S_FlAG='0'
                         
                --修改 客户提成表DRP_ORDER_CUS状态            
                UPDATE DRP_ORDER_CUS
                   SET IS_TRANSFER='1'
                  FROM (
                               SELECT ID
                              FROM DRP_ORDER_CUS
                             WHERE CONVERT(VARCHAR(10),DATEADD(day,FREEZE_DAY,INSERTTIME),102)<=CONVERT(VARCHAR(10),GETDATE(),102)
                               AND IS_TRANSFER='0'
                               AND CHECK_STATUS='1'
                               AND S_FlAG='0'
                        )AS B 
                 WHERE DRP_ORDER_CUS.ID=B.ID            
                
                
                COMMIT TRANSACTION
            END TRY 
            
            BEGIN CATCH
                PRINT '错误'
                ROLLBACK TRANSACTION
            END CATCH
END
GO