UPDATE PART OF DATA

Question

Post reply

UPDATE PART OF DATA

Neel 7777

Right there with Babe

Points: 778
More actions
March 11, 2013 at 6:44 pm

#394363

TABLE IS LIKE
COMCOD ACTCODE ACTDESC
3306 180001 ADVANCE TO STAFF
3306 180002 ADVANCE TO OTHERS
3306 180003 ADVANCE TO SITE OFFICE
I LIKE TO CHANGE THREE DIGIT FROM THE OF ACTCODE TO 190 INSTEAD OF 180 WHERE EVERY ACTCODE HAVING 180 IN THE LEFT.
COMCOD nchar(4), ACTCODE PRIMARY KEY nvarchar(6), ACTDESC nvarchar(250)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

If I understand what you desire to do correctly, this should do the job.

CREATE TABLE T(COMCOD nchar(4), ACTCODE nvarchar(6), ACTDESC nvarchar(250),

PRIMARY KEY (ACTCODE))

INSERT INTO T

SELECT '3306', '180001','ADVANCE TO STAFF' UNION ALL

SELECT '3306', '180002','ADVANCE TO OTHERS' UNION ALL

SELECT '3306', '180003','ADVANCE TO SITE OFFICE'

UPDATE T SET ACTCODE = '190' + SUBSTRING(ACTCODE,4,3)

WHERE SUBSTRING(ACTCODE,1,3) = '180'

Results:

COMCODACTCODEACTDESC

3306 190001 ADVANCE TO STAFF

3306 190002ADVANCE TO OTHERS

3306 190003ADVANCE TO SITE OFFICE

Remember to test, and then test again before using in a Production database

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

bitbucket-25253 (3/11/2013)
CREATE TABLE T(COMCOD nchar(4), ACTCODE nvarchar(6), ACTDESC nvarchar(250), PRIMARY KEY (ACTCODE)) INSERT INTO T SELECT '3306', '180001','ADVANCE TO STAFF' UNION ALL SELECT '3306', '180002','ADVANCE TO OTHERS' UNION ALL SELECT '3306', '180003','ADVANCE TO SITE OFFICE' UPDATE T SET ACTCODE = '190' + SUBSTRING(ACTCODE,4,3) WHERE SUBSTRING(ACTCODE,1,3) = '180' Results: COMCODACTCODEACTDESC 3306 190001 ADVANCE TO STAFF 3306 190002ADVANCE TO OTHERS 3306 190003ADVANCE TO SITE OFFICE

Wouldn't STUFF be a better choice? You know you're replacing the first 3 characters, you don't know that the length of ACTCODE is always the same.

UPDATE T

SET ACTCODE = STUFF(ACTCODE, 1, 3, '190')

WHERE ACTCODE LIKE '180%';

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/