Q:

Merge sql with date condition

I have create merge stored procedures as below, what i am trying to achieve is the following scenario:

Merge the new record if ProductTRN is not exist in ProductList table (complete)
Only Update the ProductList record in  where the PU.CreateDate is bigger than CreateDate of target table which is ProductList (Not Complete)

Please advise me how I can achieve the second scenario above, thank you
CREATE PROCEDURE [dbo].[usp_ProductList_Merge]
AS 
    BEGIN

    DECLARE @retValue INT
        BEGIN TRY
            IF OBJECT_ID('ProductList') IS NOT NULL 
                BEGIN
                    BEGIN TRANSACTION MergeConsumerTable
                    SET NOCOUNT ON;
                    MERGE dbo. ProductList AS target
                        USING 
                            ( SELECT    

                    PU.ProductTRN,
                    PU.ProductName,
                                PU.ProductDescription,
                                        PU.CreateDate

                              FROM      dbo. TmpProductList PU
                              WHERE     PU.ProductTRN = ProductTRN

                            ) AS source (  
                    ProductTRN,
                                        ProductName,
                                        ProductDescription
                    CreateDate)

                        ON ( (target. ProductTRN) = LOWER(source. ProductTRN)

                           )
                        WHEN MATCHED 
                            THEN 
        UPDATE                SET
                ProductTRN= source. ProductTRN 
                        WHEN NOT MATCHED 
                            THEN    
        INSERT  (

                  ProductTRN,
                  ProductName,
                  ProductDescription,
          CreateDate

                )             VALUES
                ( 
          source. ProductTRN,
                  source. ProductName,
                  source. ProductDescription, 
                  source. CreateDate,

                );
                    DELETE  PU
                    FROM    dbo. TmpProductList PU

                    COMMIT TRANSACTION MergeProductListTable
                    SET @retValue = 1
                    SELECT @retValue
                END
            ELSE 
                BEGIN
                SET @retValue = -1
                    SELECT  @retValue
                END
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION MergeProductListTable
            DECLARE @ErrorMsg VARCHAR(MAX);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
            SET @ErrorMsg = ERROR_MESSAGE();
            SET @ErrorSeverity = ERROR_SEVERITY();
            SET @ErrorState = ERROR_STATE();
        SET @retValue = 0
        SELECT @retValue
           -- SELECT  0 AS isSuccess
        END CATCH
    END

A:

WITH Source AS (
   SELECT ProductTRN
         ,ProductName
         ,ProductDescription
         ,CreateDate
   FROM dbo. TmpProductList
)
MERGE ProductList AS Target
USING Source
      ON Target. ProductTRN = Source. ProductTRN
WHEN MATCHED
     AND Source. CreatedDate > Target. CreatedDate
THEN UPDATE SET
    ProductName = Source. ProductName
   ,ProductDescription = Source. ProductDescription
   ,CreateDate = Source. CreatedDate
WHEN NOT MATCHED BY TARGET
THEN INSERT (
    ProductTRN
   ,ProductName
   ,ProductDescription
   ,CreateDate
  )
  VALUES (
    Source. ProductTRN 
   ,Source. ProductName
   ,Source. ProductDescription
   ,Source. CreatedDate
  )

