UPDATE with CASE statement problem

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

General SQL Server Forums

New to SQL Server Programming

UPDATE with CASE statement problem

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-13 : 16:23:18

select case
when first_name is null then
last_name
when middle_name is null and title is not null then
first_name + ' ' + last_name
when middle_name is null and title is null then
first_name + ' ' + last_name
when middle_name = ' ' then
first_name + ' ' + last_name
when middle_name is not null and title is null then
first_name + ' ' + substring(middle_name,1,1) + ' ' + last_name
when middle_name is not null and title is not null then
first_name + ' ' + substring(middle_name,1,1) + ' ' + last_name
end as New_Name,

name as Old_Name, last_name, first_name, middle_name, title, upin, ss_dd_Physician_id, providerid, alternatename = name

--update dd_physician_lookup set name = (case
when first_name is null then last_name
when middle_name is null and title is not null then first_name + ' ' + last_name
when middle_name is null and title is null then first_name + ' ' + last_name
when middle_name = ' ' then first_name + ' ' + last_name when middle_name is not null and title is null then first_name + ' ' + substring(middle_name,1,1) + ' ' + last_name
when middle_name is not null and title is not null then first_name + ' ' + substring(middle_name,1,1) + ' ' + last_name
end)
from dd_physician_lookup

I have this UPDATE query with a CASE statement that wouldn't run and comes up with the message
- "Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated."
And I just couldn't figure out what's wrong. Basically, the case statement in my SELECT statement is the same CASE statement in my UPDATE statement. Any help would be greatly appreciated. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 16:27:41

Your [Name] column is not big enough to fit the potentially large concatenated value that results from your expression.
It is usually not a good idea to store that expression since you have all of it components stored. No need for both and it is better to have the individual constituents broken out.

Be One with the Optimizer
TG

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-13 : 17:12:28

OK. I did away with the case statement and tried this simple update statement -
update dd_physician_lookup set alternatename = name, name = (first_name + ' ' + last_name)
from dd_physician_lookup
where name is not null
and first_name is not null --4621
and middle_name is null --1243

And I am still getting the same error message. Thanks for any help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 17:23:04

what is the column definition for [name] ?
And what are the defs for [first_name] and [last_name] ?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 17:25:54

You'll either need to increase the size of the [name] column or wrap your expression in a SUBSTRING or LEFT function. Of course then you will end up with partial "truncated" names

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources