UPDATE IF NULL ELSE DO NOTHING

• Michael_O

  Ten Centuries

  Points: 1244

  More actions

  February 13, 2017 at 11:02 am

  #401609

  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!!!

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  February 13, 2017 at 2:05 pm

  #1928211

  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.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• Michael_O

  Ten Centuries

  Points: 1244

  More actions

  February 13, 2017 at 5:36 pm

  #1928253

  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!!!

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  February 14, 2017 at 7:23 am

  #1928356

  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.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)