Problems Updating...... - SQL Server Forums

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)

Problems Updating......

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-08-13 : 14:09:46

I am trying to update multiple job title rows using the following SQL statement:

UPDATE contact
SET job_title = REPLACE(job_title,'AVP','Assistant Vice President') where job_title LIKE '%AVP%'

I get the following error when running this query:
String or binary data would be truncated.
The statement has been terminated.

My data type used is varchar(50); I can't make this any longer.
Is there a way to update each row and skip the rows where the character limit is a factor? Right now, it looks like the whole update query is abandoned once an excessive lenght is experienced.

Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:21:49

I do not get any error when i run this

create table contact (job_title varchar(50))

insert into job values ('AVP')
insert into job values ('AVP')
insert into job values ('AVP')
insert into job values ('AVP')
insert into job values ('AVP')

UPDATE job
SET job_title =
REPLACE(job_title,'AVP','Assistant Vice President') where job_title LIKE '%AVP%'

can you post your table structure and some sample data.

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 14:29:05

quote:
Originally posted by qman

I am trying to update multiple job title rows using the following SQL statement:

UPDATE contact
SET job_title = REPLACE(job_title,'AVP','Assistant Vice President') where job_title LIKE '%AVP%'

I get the following error when running this query:
String or binary data would be truncated.
The statement has been terminated.

My data type used is varchar(50); I can't make this any longer.
Is there a way to update each row and skip the rows where the character limit is a factor? Right now, it looks like the whole update query is abandoned once an excessive lenght is experienced.

Thanks

Perhaps you already have some data in the column that when concatenated with "'Assistant Vice President" is > varchar(50).

Try this:


select REPLACE(job_title,'AVP','Assistant Vice President') 
  , len(REPLACE(job_title,'AVP','Assistant Vice President'))
From contact
where job_title LIKE '%AVP%'

and notice the lengths.. you can see where/why the error occurs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-08-13 : 14:30:39

Thanks for the reply Ashley.....

I am trying to update/remove "AVP" and insert "Assistant Vice President" which would cause it to be past the character limit of 50. I was hoping the update statement would update all rows and skip rows that were past the limit. See current data below.
The table field looks like this: Job_Title (varchar(50), null)

Avp Underwriting
Avp Underwriting
Avp Administration
AVP-Risk Management
Avp International Life Und.
Avp
AVP of Operations
AVP of Claims
AVP Life and Health Claims
AVP of Claims
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP, Claims Administration
AVP of Claims
AVP of Claims
AVP of Claims
AVP of Claims
AVP of Claims
AVP of Claims
AVP, Claims & Compliance
AVP, Claims Administration
AVP - Customer Operations
AVP, Corporate Underwriting
AVP Chief Actuary
AVP of Operations
AVP Life Underwriting - ABCDEDF
AVP. Ins Oper. WEB
AVP Life Underwriting ABCDEDF
AVP New Business Underwriting
AVP Chief Underwriter
AVP Information Systems
AVP & Chief Underwriter
AVP
AVP of Technical Development

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-13 : 14:37:36

This can probably help:


update Contract
Set Job_title = Case When Len(REPLACE(Job_title,'AVP','Assistant Vice President')) <= 50 Then REPLACE(Job_title,'AVP','Assistant Vice President')
					Else Job_title End
From contact
where job_title LIKE '%AVP%'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-08-13 : 14:40:29

Thanks a bunch Dinakar, problem solved!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources