SQL server update statement getting Null

Question

UPDATE Newchat
 Set [Answered] = (Select b.[Answered]
      From Table1 b
      where  Newchat.CHAT_ID = b.[Chat ID]
      and Newchat.Chat_Date >= '2017-Feb-22' and Newchat.Chat_Date <= '2017-Feb-28');

Whenever I'm running this code all rows of [Answered] are getting Null except this dates. Please suggest someway.

I also tried applying filter "Newchat.[Answered] is not null"

UPDATE Newchat
     Set [Answered] = (Select b.[Answered]
          From Table1 b
          where  Newchat.CHAT_ID = b.[Chat ID] and Newchat.[Answered] is not null
          and Newchat.Chat_Date >= '2017-Feb-22' and Newchat.Chat_Date <= '2017-Feb-28');

This too doesn't work.

Gordon Linoff · Accepted Answer · 2017-03-21 11:31:33Z

1

When there is no match, you get NULL. Presumably you really want:

UPDATE nc
    Set [Answered] = b.[Answered]
    From NewChat nc join
         Table1 b
         on nc.CHAT_ID = b.[Chat ID] and
            nc.Chat_Date >= '2017-Feb-22' and nc.Chat_Date <= '2017-Feb-28';

This only updates the rows that match the conditions.

answered Mar 21, 2017 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

aucuparia · Accepted Answer · 2017-03-21 11:45:40Z

1

You have two queries going on here. The inner one (inside the brackets) is supplying the data for the outer one which is doing the update. Your problem is that the inner query is restricted by dates, so it only supplies values for those dates. But the outer query updates all rows - with NULL where there is no data from the inner query.

Keeping close to the way you have set out your query, moving the date restrictions outside the brackets should give you what you want - like this:

UPDATE Newchat
 Set [Answered] = ( Select b.[Answered]
      From Table1 b
      where  Newchat.CHAT_ID = b.[Chat ID] )
 WHERE Newchat.Chat_Date >= '2017-Feb-22' and Newchat.Chat_Date <= '2017-Feb-28';

edited Mar 21, 2017 at 11:45

answered Mar 21, 2017 at 11:37

aucuparia

2,05121 silver badges27 bronze badges

Comments

FelixSFD · Accepted Answer · 2017-03-21 16:13:20Z

0

Try below UPDATE statement:

UPDATE Newchat SET [Answered] = b.[Answered]
From Table1 b
WHERE Newchat.CHAT_ID = b.[Chat ID] AND DATEDIFF(DAY,Newchat.Chat_Date,'2017- 
02-22') <= 0 AND DATEDIFF(DAY,Newchat.Chat_Date,'2017-02-28') >= 0

edited Mar 21, 2017 at 16:13

FelixSFD

6,10110 gold badges46 silver badges135 bronze badges

answered Mar 21, 2017 at 11:32

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL server update statement getting Null

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related