title: update oldID field based on fields in the same table
body: <p>I need help with the following query.</p>
  
  <pre><code>create table #table1
  (id int not null primary key identity,
  customer_name varchar(25),
  usage float,
  oldID int null
  )
  
  
  insert into #table1 values('ABC',46.5,null)
  insert into #table1 values('ABC',46.5,null)
  insert into #table1 values('DEF',36.8,null)
  insert into #table1 values('XYZ',50.1,null)
  insert into #table1 values('DEF',36.8,null)
  insert into #table1 values('XYZ',50.1,null)
  
  select * from #table1
  </code></pre>
  
  <p>I want my table to be updated like this</p>
  
  <pre><code>id          customer_name             usage                  oldID
  ----------- ------------------------- ---------------------- -----------
  1           ABC                       46.5                   NULL
  2           ABC                       46.5                   1
  3           DEF                       36.8                   NULL
  4           XYZ                       50.1                   NULL
  5           DEF                       36.8                   3
  6           XYZ                       50.1                   4
  </code></pre>
  
  <ol>
  <li>The two records with the same name and usage means the later record was renewed. </li>
  <li>In the new record the oldID field should point to its old record (ID).</li>
  </ol>
  
  <p>Although in my actual table, I have a bunch of date fields which I probably can use but this would help me for now.</p>