Doesnt update records correctly

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 2000 Forums

Transact-SQL (2000)

Doesnt update records correctly

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2003-05-12 : 08:26:09

Could someone look at this code and tell me what I'm doing wrong. I'm combining 5 fields from one table to 1 field of another. When I comment out all except the first line it updates it. With out the comments it updates it to <NULL> This is driving me crazy.

/* Update Current Records in AppUser Table */

update appuser
set
Forename = dbo.ProperCase(Appuser_Temp.ForeName),
Surname = dbo.ProperCase(Appuser_Temp.surName),
Job_Title = dbo.ProperCase(Appuser_Temp.Job_Title),
EMPLOYMENT_START= appuser_temp.Employment_start,
Office_Phone = Appuser_Temp.Office_AreaCode +' '+ Appuser_Temp.Office_Phone,
Address = dbo.ProperCase(Appuser_Temp.Address1) + char(13)+ char(10)+
dbo.ProperCase(Appuser_Temp.Address2)+ char(13)+ char(10)+
dbo.ProperCase(Appuser_Temp.City)+ ' ' + Appuser_Temp.State + Appuser_Temp.PostalCode,
Home_Phone = Appuser_Temp.Home_AreaCode+' '+ Appuser_Temp.Home_Phone,
Email_Address = Appuser_Temp.Email_Address,
Emergency_Contact = dbo.ProperCase (Appuser_Temp.Emergency_Contact1)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact2)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact3)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact4)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact5)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact6)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact7)+ char(13)+ char(10)+
dbo.ProperCase (Appuser_Temp.Emergency_Contact8),
DOB = Appuser_Temp.DOB,

Sex = Appuser_Temp.Sex,
Employment_End = appuser_temp.Employment_End,
Category_ID = Appuser_Temp.Category_ID,
Mobile_Phone = Appuser_Temp.Mobile_AreaCode+ Appuser_Temp.Mobile_Phone,
LastUpdate_Date= getdate(),
LastUpdate_User = 'JDE Upload'

from appuser join Appuser_Temp on (appuser.Employee_Number = ltrim(Appuser_Temp.Employee_Number))

I do have + between the fields

Thank God for Forums.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-12 : 08:30:00

If you have nulls in some of the strings you are concatenating, you have to use isnull or coalesce. null + 'hdaf' = null

vwilsonjr
Starting Member

45 Posts

Posted - 2003-05-12 : 11:02:07

I will have nulls so what do I need to do. Is this it?

dbo.ProperCase (Appuser_Temp.Emergency_Contact1)+ char(13)+ char(10)+ null + 'hdaf' = null +
dbo.ProperCase (Appuser_Temp.Emergency_Contact2)+ char(13)+ char(10)+ 'hdaf' = null +
dbo.ProperCase (Appuser_Temp.Emergency_Contact3)+ char(13)+ char(10)+ 'hdaf' = null

Thank God for Forums.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-12 : 14:07:44

No, what he meant was to avoid concatenating nulls with strings, and that is accomplished using either ISNULL() or COALESCE() to replace NULLs with blank strings. For example

SELECT ISNULL(Emergency_contact_1, '') + Char(13) + Char(10) +
ISNULL(Emergency_contact_2, '') AS emergency_contacts FROM BlahTable

EDIT: If you leave out the ISNULL, the column emergency_contacts will return a null if either Emergency_contact_1 or Emergency_contact_2 is null.

OS

Edited by - mohdowais on 05/12/2003 14:08:26

vwilsonjr
Starting Member

45 Posts

Posted - 2003-05-12 : 14:43:33

Thanks that did it. TAL

Thank God for Forums.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-12 : 22:25:39

Another simpler (possibly) way:
issue the SET CONCAT_NULL_YIELDS_NULL OFF statement first, and concatenating string(s) with null will not result in a null, but in the string(s).
Dont forget to SET CONCAT_NULL_YIELDS_NULL ON after your other statements to set the option back to its default.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources