UPDATE Record's

[JSuresh]

Hi Dude's,

i want to update the cutomer table in customer id UA001 to UA0001, UA100 to UA0100 like that all records, i have plenty of records in this table please help me. I am using Sql Server 2005.


Thanks & Regards,
J Suresh

[ck9663]

Find it here

Just use the WHERE clause.

-- CK

[Delerna]

If I understand your question correctly, then

UPDATE CustomerTable SET CustID=left(CustID,2) + '0' + right(CustID,len(CustID)-2)
 

you might want to test that with a select before running the update first.

SELECT CustID,left(CustID,2) + '0' + right(CustID,len(CustID)-2) as NewCustID
FROM CustomerTable
 

if the NewCustID looks OK, then you can run the update.

[JSuresh]

Hi,

Thanks for your reply, When i use the SELECT query its working fine,

Party_id(char-15) Customer_id(varchar-15)
SA00001 SA000001 *******
SA00002 ******* SA000002 *******
SA00003 ******* SA000003 *******
SA00004 ******* SA000004 *******
SA00005 ******* SA000005 *******

While try to UPDATE its showing error becoz its a varchar(15) datatype.

I place the error below.

Error :
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Please help me in this

If I understand your question correctly, then

UPDATE CustomerTable SET CustID=left(CustID,2) + '0' + right(CustID,len(CustID)-2)
 

you might want to test that with a select before running the update first.

SELECT CustID,left(CustID,2) + '0' + right(CustID,len(CustID)-2) as NewCustID
FROM CustomerTable
 

if the NewCustID looks OK, then you can run the update.

[Delerna]

Are you sure its a varchar(15) and not char(15)

If it's char(15) then try this

UPDATE CustomerTable SET CustID=left(rtrim(CustID),2) + '0' + right(CustID,len(rtrim(CustID))-2)
 

char(15) will have 15 characters.....always. So the original update query would make it 16 characters and therefore generate the error.

If it is varchar(15) then one or more fields has 15 characters in them and adding the extra character makes 16 and therefore the error.
In this case you will need to chage the field in the table so it is varchar(16)

[JSuresh]

Thank you ,

Its working excelent.

Thanks & Regards,
JSuresh

Are you sure its a varchar(15) and not char(15)

If it's char(15) then try this

UPDATE CustomerTable SET CustID=left(rtrim(CustID),2) + '0' + right(CustID,len(rtrim(CustID))-2)
 

char(15) will have 15 characters.....always. So the original update query would make it 16 characters and therefore generate the error.

If it is varchar(15) then one or more fields has 15 characters in them and adding the extra character makes 16 and therefore the error.
In this case you will need to chage the field in the table so it is varchar(16)