Clever Geek Handbook

How to change string data with bulk update?

I have a table (T1) in t-sql with a column (C1) that contains almost 30,000 rows of data. Each column contains values ​​like MSA123, MSA245, MSA299, etc. I need to run an update script so that the MSA part of the line changes to CMA. How can i do this?

+1


source to share


6 answers 


update t1
set c1 = replace(c1,"MSA","CMA")
where c1 like "MSA%"


+3


source


I don't have SQL Server in front of me, but I believe this will work:



UPDATE T1 SET C1 = REPLACE(C1, 'MSA', 'CMA');
+2


source


You can use REPLACE to do this.

+1


source


In addition to what fallen888 posted, if there are other values ​​in that table / column, you can use the LIKE operator in the where clause to make sure you only update the records you want:

... WHERE [C1] LIKE 'MSA[0-9][0-9][0-9]'
+1


source


While replacement will work, what happens when you need to replace M with C for MSA but not MCA? Or if you have MSAD as well as MSA in the data right now and you didn't want that to change (or CMSA). Do you even know for sure if you have any data that you don't want to replace?

The correct answer is never to store data this way. The first rule for creating a database is to store only one piece of information per field. Instead, you should have a linked table. It will become easier to maintain over time.

0


source


I have to disagree with the HLGEM post. While it is true that the first normal form speaks of atomicity in EF Codd's original vision (this is the most controversial aspect of 1NF IMHO), the original query does not necessarily mean that there are no related tables or that the value is not atomic.

The MSA123 may be the natural key of the object in question, and the company may have simply decided to rename its product line. It's correct to say that if an artificial identifier was used, then even updates to the natural key would not require updating as many rows as possible, but if that's what you mean, I would say that artificial keys are definitely not the first rule of database design. They have their advantages, but they also have many disadvantages that I will not go into here, but a small search engine might run into disagreement as to whether artificial primary keys should be used.

As far as the original request goes, others have already nailed it, REPLACE is the way to go.

0


source






More articles:


All Articles