question

spatel avatar image
spatel asked

spelling mistake

![alt text][1]Hi after inserting a bulk values in a table, you find either a spelling mistake or think of changing to a sentance case change. is it possible to update the table. I know 1 way is to drop and recreate but what if thing go wrong once table dropped and something happend like you forgot the columns names or data type or so. [1]: /storage/temp/701-sql.gif
drop
sql.gif (50.7 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
So you want to change "product" (all lower case) to "Product" (capital P)? And you want a generic way of doing this?
0 Likes 0 ·
spatel avatar image spatel commented ·
yes, don't know how would this can be done. i can't do update table (tablename) set - this will be completely wrong.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Your SELECT statement that you were talking about in response to m'learned colleague Mr @Grant Fritchey should look like: SELECT ID, REPLACE(Name, 'product', 'Product'), REPLACE (Description, 'product', 'Product') So we wrap that into a UPDATE...FROM statement like this: DECLARE @TableData TABLE ( ID INT IDENTITY(1, 1) , ProdName VARCHAR(20) , ProdDesc VARCHAR(100) ) INSERT INTO @TableData ( ProdName, ProdDesc ) VALUES ( 'product-1', 'product-1 Description' ), ( 'product-2', 'product-2 Description' ) SELECT * FROM @TableData UPDATE @TableData SET t.ProdName = tup.ProdName , T.ProdDesc = tup.proddesc FROM @TableData T INNER JOIN ( SELECT ID , REPLACE(ProdName, 'prod', 'Prod') AS ProdName , REPLACE(ProdDesc, 'prod', 'Prod') AS ProdDesc FROM @TableData ) AS tup ON T.ID = tup.id SELECT * FROM @TableData
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Oh sure, show off. Ha! Well done. +1
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What, no snark about me missing out semicolons? ;-)
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Nah, I'll be over there in May and June and we'll discuss it in person.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I'll bring body armour.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Assuming I understand the question, if you're talking about fixing the values inside the columns, you could use the REPLACE function. [There's more here on MSDN][1]. If you're talking about the objects within the database (since you said drop & recreate, this might be a possibility) you can use sp_rename for most of them. Again, [MSDN has the answers.][2] [1]: http://msdn.microsoft.com/en-US/library/ms186862(v=sql.110).aspx [2]: http://msdn.microsoft.com/en-us/library/ms188351.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

spatel avatar image spatel commented ·
I tried replace, am putting it all wrong, getting syntax error select tablename.columname replace ('product','p', 'P') from TableName
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.