Question on Performing a Mass Update

[lrdave36]

Hey guys,

I don't wanna mess with update statements unless I'm 100% sure this will work.

The requestion is simple. I need to add the value 0 to end of each three character code name.

Here is a sample of the table:


code description
____ ____________

UAK UKRAINE
VEB VENEZUELAN BOLIVAR
WST TALA



After the update, the table would look like:


code description
____ ____________

UAK0 UKRAINE
VEB0 VENEZUELAN BOLIVAR
WST0 TALA


Is there a way to make this change in one mass update?

 

[gmmastros]

Update YourTableName
Set    Code = Code + '0'
Where  Len(Code) = 3

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Just as a tip, when not certain, write and execute your UPDATE statements as SELECT statemens first. If you' re satisfied with the results then change the SELECT statements to UPDATE stataments. So in your case, using George' s solution, you would write:

SELECT Code + '0' FROM YourTableName WHERE  LEN(Code) = 3

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[lrdave36]

Thanks guys! I ran the query, and was surprised to get this error message:

‘String or binary data would be truncated’

The code field is set to char(10) so I don't see any reason why SQL would complain. Any thoughts?


Great tip, Bug!

 

[gmmastros]

Char columns are padded with spaces, so you'll need to add an RTrim in there.

Update YourTableName
Set    Code = RTrim(Code) + '0'
Where  Len(Code) = 3

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Thanks, it worked great!