Update query with ISNULL in SQL SERVER

Question

DateTime Should update if the data is passed else update it with original value which is already saved. This update does not works

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
  SET   
      FolderStatusDate = ISNULL(@FolderStatusDate, FolderStatusDate)
      WHERE Id = @Id

The question is tagged SQL Server. AFAIK, ifnull is MySql and is not available in SQL Server — Raj
– Raj, Commented Jun 11, 2014 at 4:15
The script in the question will not work because @id is neither declared nor set, but the query seems correct. What "does not works" mean? You get an error, the data is not updated when it should, or the opposite, or what else? — Serpiton
– Serpiton, Commented Jun 11, 2014 at 8:12
Side note: It's a good habit to get into to write "literal" dates with T as the separator rather the space, e.g. '2012-07-04T14:09:04.043' - '2012-07-04 14:09:04.043' can be interpreted (under weird circumstances) as either yyyy-mm-dd... or yyyy-dd-mm... — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 11, 2014 at 9:54
To expand on @Damien's point, there's an alternative way to represent a timestamp unambiguously – just omit the hyphens (-) in the date part: 20120704 14:09:04.043. It is true that either method makes the original form less readable, but I believe consistency is more important and at least you've got more than one unambiguous format to choose from. — Andriy M
– Andriy M, Commented Jun 11, 2014 at 10:35

Raj · Accepted Answer · 2014-06-11 08:28:22Z

1

Why don't you move the check for NULL to the WHERE clause?

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
  SET   
      FolderStatusDate = @FolderStatusDate
      WHERE Id = @Id  
      AND @FolderStatusDate IS NOT NULL

edited Jun 11, 2014 at 8:28

answered Jun 11, 2014 at 4:12

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Serpiton Over a year ago

Shouldn't the IS NULL check be on @FolderStatusDate? (and be IS NOT NULL)

Andriy M Over a year ago

Your query is identical to the OP's in terms of the final data state. The only difference is that your query would skip updating any rows if @FolderStatusDate was NULL instead of overwriting the column with same values. Seems a good measure in terms of performance optimisation but how does it solve the problem at hand (which is not even clear at this point)?

Hitesh · Accepted Answer · 2014-06-11 09:55:20Z

0

You can also do it this way

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
SET   
  FolderStatusDate = case when ISNULL(@FolderStatusDate, '') = '' 
                           then FolderStatusDate else @FolderStatusDate end
  WHERE Id = @Id

answered Jun 11, 2014 at 9:55

Hitesh

3,5048 gold badges43 silver badges61 bronze badges

Comments

Rajesh · Accepted Answer · 2014-06-11 11:56:29Z

0

A slight edit on Hitesh Salian's answer

 DECLARE @FolderStatusDate DATETIME = NULL

 SET @FolderStatusDate = '2012-07-04 14:09:04.043'

 UPDATE CM.PfmFolder     
 SET   
 FolderStatusDate = case @FolderStatusDate is Null
                       then FolderStatusDate else @FolderStatusDate end
 WHERE Id = @Id

answered Jun 11, 2014 at 11:56

Rajesh

1,6305 gold badges36 silver badges63 bronze badges

Collectives™ on Stack Overflow

Update query with ISNULL in SQL SERVER

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related