UPDATE IF NULL ELSE DO NOTHING

  • Hello Team,
    I need to update a column of a table if it's null and do nothing if it's not null, not sure how to script that logic. Need some help

    Table: [dbo].[ReplicationLatencyCheck]

    Columns: [PublisherTime], [SubscriberTime], [ReplTime (Secs)]

    1. I will have an agent job executing every minute to insert a getdate() in to [PublisherTime]--- at Server A

    2. Need Second agent job executing continuously to check if [SubscriberTime] IS NULL--- at Server B

    3. IF NULL it will insert a getdate() into [SubscriberTime] and calculate the difference and insert that into [ReplTime (Secs)]

    This is what i already have:

    USE DBATools
    GO

    INSERT INTO [dbo].[ReplicationLatencyCheck] (PublisherTime) VALUES (GETDATE())--- at Server A

    UPDATE [dbo].[ReplicationLatencyCheck] SET SubscriberTime = (GETDATE()) WHERE SubscriberTime IS NULL--- at Server B

    --WAITFOR DELAY '00:00:05';

    UPDATE [dbo].[ReplicationLatencyCheck]
    SET
    [ReplTime (Secs)] = (SELECT DATEDIFF(second, PublisherTime, SubscriberTime)
    FROM [DBATools].[dbo].[ReplicationLatencyCheck] WHERE [ReplTime (Secs)] IS NULL)
    WHERE [ReplTime (Secs)] IS NULL

    I need to be able to wrap up the last 2 update statement in a way that the updates only happen if SubscriberTime is NULL, I believe I'm very close. But i want to be able to avoid alerts/agent job failures since the job at subscriber will execute continuously

    THANKS IN ADVANCE!!!

  • Most everything I see in your post appears okay, except perhaps the UPDATE.   Seems more appropriate to do it this way:


    UPDATE RLC
    SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
    FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
    WHERE RLC.[ReplTime (Secs)] IS NULL;

    There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening.  Much better to use a table alias as the target for the UPDATE keyword.   Jeff Moden was the post author, btw.

  • sgmunson - Monday, February 13, 2017 2:05 PM

    Most everything I see in your post appears okay, except perhaps the UPDATE.   Seems more appropriate to do it this way:


    UPDATE RLC
    SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
    FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
    WHERE RLC.[ReplTime (Secs)] IS NULL;

    There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening.  Much better to use a table alias as the target for the UPDATE keyword.   Jeff Moden was the post author, btw.

    Thank you!!!

  • MVP_enthusiast - Monday, February 13, 2017 5:36 PM

    sgmunson - Monday, February 13, 2017 2:05 PM

    Most everything I see in your post appears okay, except perhaps the UPDATE.   Seems more appropriate to do it this way:


    UPDATE RLC
    SET RLC.[ReplTime (Secs)] = DATEDIFF(second, PublisherTime, SubscriberTime)
    FROM [DBATools].[dbo].[ReplicationLatencyCheck] AS RLC
    WHERE RLC.[ReplTime (Secs)] IS NULL;

    There are some recent posts on this site that talk about UPDATE performing badly when an IMPLICIT JOIN is happening.  Much better to use a table alias as the target for the UPDATE keyword.   Jeff Moden was the post author, btw.

    Thank you!!!

    Glad I could help.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply