title: SQL UPDATE WHERE IN (List) or UPDATE each individually?
body: <p>I'm doing my best lately to look for the best way to run certain queries in SQL that could potentially be done multiple different ways. Among my research I've come across quite a lot of hate for the WHERE IN concept, due to an inherent inefficiency in how it works.</p>
  
  <p>eg:  <code>WHERE Col IN (val1, val2, val3)</code></p>
  
  <p>In my current project, I'm doing an UPDATE on a large set of data and am wondering which of the following is more efficient: (or whether a better option exists)</p>
  
  <pre><code>UPDATE table1 SET somecolumn = 'someVal' WHERE ID IN (id1, id2, id3 ....);
  </code></pre>
  
  <p>In the above, the list of ID's can be up to 1.5k ID's.</p>
  
  <p>VS</p>
  
  <p>Looping through all ID's in code, and running the following statement for each:</p>
  
  <pre><code>UPDATE table1 SET somecolumn = 'someVal' WHERE ID = 'theID';
  </code></pre>
  
  <p>To myself, it seems more logical that the former would work better / faster, because there's less queries to run. That said, I'm not 100% familiar with the in's and out's of SQL and how query queueing works. </p>
  
  <p>I'm also unsure as to which would be friendlier on the DB as far as table locks and other general performance.</p>
  
  <p>General info in case it helps, I'm using Microsoft SQL Server 2014, and the primary development language is C#.</p>
  
  <p>Any help is much appreciated.</p>
  
  <p>EDIT:</p>
  
  <p>Option 3: </p>
  
  <pre><code>UPDATE table1 SET somecolumn = 'someVal' WHERE ID IN (SELECT ID FROM @definedTable);
  </code></pre>
  
  <p>In the above, @definedTable is a SQL 'User Defined Table Type', where the data inside comes through to a stored procedure as (in C#) type SqlDbType.Structured</p>
  
  <p>People are asking how the ID's come in:
  ID's are in a <code>List&lt;string&gt;</code>in the code, and are used for other things in the code before then being sent to a stored procedure. Currently, the ID's are coming into the stored procedure as a 'User-Defined Table Type' with only one column (ID's).</p>
  
  <p>I thought having them in a table might be better than having the code concatenate a massive string and just spitting it into the SP as a variable that looks like <code>id1, id2, id3, id4</code> etc</p>