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?

NoobCoder
  • 117
  • 1
  • 11
  • Try using a cursor https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/ (quick note: cursor performance is not so good) – rlm96 Jul 08 '20 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. – SMor Jul 08 '20 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. – SMor Jul 08 '20 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. https://stackoverflow.com/questions/16818796/generate-sql-insert-script-from-excel-worksheet/16819629 – Alex Jul 08 '20 at 23:50

0 Answers0