Help with script to update data in two columns

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 2008 Forums

Transact-SQL (2008)

Help with script to update data in two columns

Author

Topic

holzert
Starting Member

9 Posts

Posted - 2011-05-11 : 11:03:40

I have multiple tables that had bad data stored for a short period of time. The inserting of new data has been resolved but I need to fix the data in some records that got stored during that time period. It is 2 columns in about 10 tables.

Columns:

myid PK, varchar(14), not null)
mypic varchar(14), not null)

A correct value for both would be:

myid 2011004085
mypic 9999-999999

For a period of time, some records were inserted with these values:

myid 20110004085 <--value unique for each record but same format
mypic 9999-9999999 <--a display format to use for showing myid

Also, in some cases, the myid value was stored correctly (2 zeros in middle, not three, but others it has three.

I basically need to remove one of the 3 zeros in the middle of myid and the 9 on the end of mypic in all of those records.

I need to do this for multiple tables (same column names in each table, however some tables do not have the mypic column)

Any help appreciated. I need to produce a script to do this on multiple systems.

TIA,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 11:28:27

DECLARE @myID varchar(20)
SET @myID = '201100004085'

DECLARE @myPic char(12)
SET @myPic = '9999-9999999'

SELECT stuff(@myID,charindex('000',@myID),1,'')--< pretty safe
,convert(char(11),@mypic) --< only if everything in that column is now 12 characters
,substring(@mypic,1,len(@myPic)-1) -- little safer

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-11 : 11:29:10

Do you mean: I basically need to remove ~~one~~ two of the 4 zeros in the middle of myid ?
Do you have a SELECT that is able to get only thos rows where the data is wrong?

No, you're never too old to Yak'n'Roll if you're too young to die.

holzert
Starting Member

9 Posts

Posted - 2011-05-11 : 11:35:12

quote:
Originally posted by jimf

DECLARE @myID varchar(20)
SET @myID = '201100004085'

DECLARE @myPic char(12)
SET @myPic = '9999-9999999'

SELECT stuff(@myID,charindex('000',@myID),1,'')--< pretty safe
,convert(char(11),@mypic) --< only if everything in that column is now 12 characters
,substring(@mypic,1,len(@myPic)-1) -- little safer

Jim

Everyday I learn something that somebody else already knew

Thank you, however, the myid value is different in every record. So basically I need to find those records that have 3 zeroes in the middle of myid, not 2 and remove a zero:

20110004444 (could be 20110004445, 20110004446, etc)
will become
2011004444
and for mypic (this is always same value, not unique):
9999-9999999
will become
9999-999999

so I could select where mypic = '9999-9999999' or len(mypic)>11 and then look at the myid value and if two many zeroes in middle of value, fix it but always fix the mypic to be '9999-999999' for those records.

holzert
Starting Member

9 Posts

Posted - 2011-05-11 : 11:36:34

quote:
Originally posted by webfred

Do you mean: I basically need to remove ~~one~~ two of the 4 zeros in the middle of myid ?
Do you have a SELECT that is able to get only thos rows where the data is wrong?

No, you're never too old to Yak'n'Roll if you're too young to die.

Exactly. that is the tricky part I can't figure out. And then always fix the mypic to be correct (see other post)

thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 11:38:14

That's what my suggestions do.

Jim

Everyday I learn something that somebody else already knew

holzert
Starting Member

9 Posts

Posted - 2011-05-11 : 13:29:49

quote:
Originally posted by jimf

DECLARE @myID varchar(20)
SET @myID = '201100004085'

DECLARE @myPic char(12)
SET @myPic = '9999-9999999'

SELECT stuff(@myID,charindex('000',@myID),1,'')--< pretty safe
,convert(char(11),@mypic) --< only if everything in that column is now 12 characters
,substring(@mypic,1,len(@myPic)-1) -- little safer

Jim

Everyday I learn something that somebody else already knew

Thank you! You got me on the right road...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 13:37:40

You're welcome. Safe travelling!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources