title: Update multiple columns by loop?
body: <p>I have a select statement which I want to convert into an update statement for all the columns in the table which have the name <code>Variable[N]</code>.</p>
  
  <p>For example, I want to do these things:</p>
  
  <ol>
  <li>I want to be able to convert the SQL below into an update statement.</li>
  <li>I have <code>n</code> columns with the name <code>variable[N]</code>. The example below only updates column <code>variable63</code>, but I want to dynamically run the update on all columns with names <code>variable1</code> through <code>variableN</code> without knowing how many <code>variable[N]</code> columns I have in advance. Also, in the example below I get the updated result into <code>NewCol</code>. I actually want to update the respective variable column with the results if possible, <code>variable63</code> in my example below.</li>
  </ol>
  
  <p>I want to have a wrapper that loops over column <code>variable1</code> through <code>variableN</code> and perform the same respective update operation on all those columns:</p>
  
  <pre><code>SELECT  
       projectid
      ,documentid
      ,revisionno
      ,configurationid
      ,variable63
      ,ISNULL(Variable63, 
              (SELECT TOP 1 
               variable63 
               FROM table1 
               WHERE 
                   documentid = t.documentid 
               and projectid=t.projectid
               and configurationid=t.configurationid 
               and cast(revisionno as int) &lt; cast(t.revisionno as int) 
               AND Variable63 is NOT NULL 
               ORDER BY 
                projectid desc
               ,documentid desc
               ,revisionno desc
               ,configurationid desc
               )) as NewCol
  FROM    table1 t;
  </code></pre>