0

So i have a pretty large table, 300+ columns. The first column is an ID column e.g. 9001, 9002 etc

The rest of the columns are int columns and they all have a value of '100', e.g. below:

9001, 100, 100, 100, 100, 100
9002, 100, 100, 100, 100, 100
9003, 100, 100, 100, 100, 100

What i want to do is where ID = 9001, i want to replace all values that are '100' with '200'

My question is: is it possible to replace all the values in the table that meets the where clause criteria.

At the moment what i am doing is essentially:

set 
[column1] = replace ([column1], '100', '200'),
[column2] = replace ([column1], '100', '200'),
[column3] = replace ([column1], '100', '200')
where id = 9001

set
[column1] = replace ([column1], '100', '200'),
[column1] = replace ([column1], '100', '200'),
[column1] = replace ([column1], '100', '200'),
where id = 9002

As you can see this will take me forever to write for 1000s of IDs and 300+ columns, is there a quicker way of doing this?

Improve this question
4
  • Try using a cursor sqlservertutorial.net/sql-server-stored-procedures/… (quick note: cursor performance is not so good) Commented Jul 8, 2020 at 15:59
  • 1
    It seems you don't understand what the replace function does. If you simply want to change the value of a column from 100 to 200, you assign the desired value. And you should use literals that are interpreted as the correct datatype - don't use string literals to assign numeric values to numeric columns. Commented Jul 8, 2020 at 16:00
  • 1
    And the short answer to what I think your question is - you must write a giant update statement to reference each of your 300+ columns. You could write code to write your query - but that is likely beyond your abilities. Commented Jul 8, 2020 at 16:01
  • You can generate update statements for SQL Server quite easily with the help of Excel, no need to write code e.g. stackoverflow.com/questions/16818796/… Commented Jul 8, 2020 at 23:50
Related questions
1
Find and replace column values with Microsoft SQL?
2
SQL Replace all values in database, no matter column and table
2
Query to find and replace a specific text in SQL Database (All tables and All Columns)
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.