title: SQL Scanning table to update the value
body: <p>I have following table</p>
  
  <pre><code>Key ID  Value  
  1    1    C  
  2    1    C  
  3    1    I  
  4    2    C  
  5    2    C  
  6    2    C  
  7    1    C 
  </code></pre>
  
  <p>If the value of value column is I then want to update previous records of same <code>ID</code> to <code>I</code>.</p>
  
  <p>For example <code>ID</code> 1 has last recod with value <code>I</code> so want to update first 2 records of <code>ID</code> 1 to <code>I</code>.
  But ID 1 with Key 7 value should not change</p>
  
  <p>I can do self join and update previous records using Key value is less than current key value etc..
  But table has large number of records so it takes long time to scan through the table for each Id value.
  Can I Use lag function but offset value will be entire table.
  So not sure which is the best solution. Or is there any other option other than self join and lag.</p>
  
  <hr>