title: Update column with increasing value using a single statement in MySQL
body: <p>I have a third-party application that has been migrated from a much earlier version to the current release, and during the migration a new column has been added to the database.  This column represents the external test case identifier.  There is a unique identifier in the table for the test case record, which is hidden from the user.  When a new version of the test case is created, a new record is added to the table.  There is obviously a new id, but the external identifier remains the same so it appears to the end user that this is the same test case.  Not my model, but fair enough.</p>
  
  <p>During the migration process, that column was added and now all the test cases look like they have an id of zero.  Oops.  Well, almost all, because a few new cases have been added through the GUI and they have the correct number.  And to boot one of the test cases actually has a second version.  I've fixed that record so it has a valid external id.</p>
  
  <p>Here's a snapshot of the relevant bit of my table:</p>
  
  <pre><code>mysql&gt; select id, version, tc_external_id from tcversions order by id limit 5;
  +------+---------+----------------+
  | id   | version | tc_external_id |
  +------+---------+----------------+
  | 9454 |       1 |              0 |
  | 9455 |       1 |              5 |
  | 9456 |       1 |              0 |
  | 9457 |       1 |              0 |
  | 9458 |       1 |              0 |
  +------+---------+----------------+
  5 rows in set (0.33 sec)
  </code></pre>
  
  <p>For those of you familiar with the database, yes, this is TestLink 1.9.3.  It's a migrated database from a modified 1.7.4 system that we inherited.</p>
  
  <p>I'm looking for a MySQL UPDATE statement that will let me update the tc_external_id such that each record gets a monotonically increasing number without having to resort to writing a separate bash/perl/whatever script that executes N number of updates.  So the result would look something like the following:</p>
  
  <pre><code>mysql&gt; select id, version, tc_external_id from tcversions order by id limit 5;
  +------+---------+----------------+
  | id   | version | tc_external_id |
  +------+---------+----------------+
  | 9454 |       1 |              6 |
  | 9455 |       1 |              5 |
  | 9456 |       1 |              7 |
  | 9457 |       1 |              8 |
  | 9458 |       1 |              9 |
  +------+---------+----------------+
  5 rows in set (0.33 sec)
  </code></pre>
  
  <p>Is this possible?  My SQL foo is obviously not up to the task, and I'm happy to RTM if someone can point me to the right FM.</p>
  
  <p><strong>Edit: Co-worker came up with the following solution</strong></p>
  
  <p>A co-worker of mine provided me with the solution, which is quite nasty if I do say so myself.  The formatting is mine, he did it as one long line.  Gah.  Flame away, but it did work.</p>
  
  <pre><code>REPLACE INTO tcversions 
    SELECT @r := id as id, 
           @r1 := version as version, 
           @r2 := summary as summary, 
           @r3 := importance as importance, 
           @r4 := author_id as author_id, 
           @r5 := creation_ts as creation_ts,
           @r6 := updater_id as updater_id, 
           @r7 := modification_ts as modification_id, 
           @r8 := active as active, 
           @r9 := is_open as is_open, 
           @r10 := execution_type as execution_type, 
           @c := @c + 1 as tc_external_id, 
           @r12 := layout as layout, 
           @r13 := status as status, 
           @r14 := preconditions as preconditions 
    FROM 
      (SELECT @r := '', 
              @r1 := '', 
              @r2 := '', 
              @r3 := '', 
              @r4 := '', 
              @r5 := '', 
              @r6 := '', 
              @r7 := '', 
              @r8 :='', 
              @r9 := '', 
              @r10 := '', 
              @r11 := '', 
              @r12 := '', 
              @r13 := '', 
              @r14 := '',  
              @c := max(tcversions.tc_external_id) from tcversions ) rc, 
      (SELECT id,
              version, 
              summary, 
              importance, 
              author_id, 
              creation_ts, 
              updater_id, 
              modification_ts, 
              active, 
              is_open, 
              execution_type, 
              tc_external_id, 
              layout, 
              status, 
              preconditions 
       FROM tcversions  WHERE tc_external_id = 0  ORDER BY id) k;
  </code></pre>