SQL 2008 Merge Where Source IS NOT NULL

• para40tn

  SSChasing Mays

  Points: 605

  More actions

  July 1, 2015 at 4:40 pm

  #305533

  I have one table that contains a complete data set and another table which contains the changes to records that were updated in the last hour. I need to update table1 (target) from table2 (source) but I don't want to change a value in table1 if the value from table2 IS NULL. How can I use a MERGE but only update where the source has a valid value (other than NULL)?

  CREATE TABLE [dbo].[targ]([Id] [INT] NULL,[item1_date] [DATETIME] NULL,[item2_date] [DATETIME] NULL) ON [PRIMARY]

  CREATE TABLE [dbo].[src]([Id] [INT] NULL,[item1_date] [DATETIME] NULL,[item2_date] [DATETIME] NULL) ON [PRIMARY]

  INSERT INTO [dbo].[targ] VALUES (1,'2015-07-01 19:00','2015-07-01 19:30')

  INSERT INTO [dbo].[targ] VALUES (2,'2015-07-01 20:00','2015-07-01 20:27')

  INSERT INTO [dbo].[targ] VALUES (3,'2015-07-01 21:00','2015-07-01 21:47')

  INSERT INTO [dbo].[src] VALUES (1,'2015-08-01 19:00','2015-08-01 19:28')

  INSERT INTO [dbo].[src] VALUES (2,'2015-08-01 20:00','2015-08-01 20:16')

  INSERT INTO [dbo].[src] VALUES (3,NULL,'2015-08-01 21:23')

  I need the merge to update [dbo].[targ] item1_date and item2_date for items 1 and 2 with the new dates from [dbo].[src] but only update item2_date for item 3 since the source for item1_date is null and the target has a value.