Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE PART OF DATA Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 6:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
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)
Post #1429555
Posted Monday, March 11, 2013 7:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 5,595, Visits: 24,985
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:
COMCOD ACTCODE ACTDESC
3306 190001 ADVANCE TO STAFF
3306 190002 ADVANCE TO OTHERS
3306 190003 ADVANCE 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

Before posting a performance problem please read
Post #1429560
Posted Tuesday, March 12, 2013 4:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 2,434, Visits: 7,513
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:
COMCOD ACTCODE ACTDESC
3306 190001 ADVANCE TO STAFF
3306 190002 ADVANCE TO OTHERS
3306 190003 ADVANCE 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%';




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1429662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse