Simple UPDATE issue - Blanks change to NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple UPDATE issue - Blanks change to NULL

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-20 : 06:36:25

Hi all,

My update script is very simple shown below:


UPDATE tblCases

SET strRef = (SELECT strRef FROM tblCheckCase WHERE tblCases.intCaseID = tblCheckCase.intCaseID)

WHERE tblCases.strRef = ''

I want to update the strRef value in tblcases where the value is a blank i.e. tblCases.strRef = ''.

The update of values works as I would expect, however all remaining records in the column tblCases.strRef that have a blank value, and don't fit the update criteria, are then updated to genuine NULL values.

I don't want this to happen, I need the original blank value to remain. Can anyone tell me what the issue is here?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 06:49:37

UPDATE tblCases

SET strRef = (SELECT coalesce(strRef,'') FROM tblCheckCase WHERE tblCases.intCaseID = tblCheckCase.intCaseID)

WHERE tblCases.strRef = ''

Madhivanan

Failing to plan is Planning to fail

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-20 : 06:57:54

thanks for the reply, but still the same issue.

It's the cases that do not join in the where clause (WHERE tblCases.intCaseID = tblCheckCase.intCaseID) that are somehow updating from '' to NULL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 07:04:44

See if this works

UPDATE t1
SET t1.strRef = t2.strRef
FROM tblCases as t1 inner join tblCheckCase as t2
on t1.intCaseID = t2.intCaseID
WHERE t1.strRef = ''

Madhivanan

Failing to plan is Planning to fail

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-20 : 07:11:19

Yes! that works perfect, thankyou. Although i'm not sure why the NULL values update the first way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-20 : 07:16:02

Because it's a correlated subquery and will return NULL if there is no match on intCaseID column.

N 56°04'39.26"
E 12°55'05.63"

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-20 : 07:31:00