title: Replacing negative id's with correct positive Ids?
body: <p>I have two tables, <code>items</code> and <code>items_archive</code>.  Both have identical columns, and the only rows you need to worry about are they both have an id row and a timestamp row which is going to be used to help order and replace the id's.</p>
  
  <p>Here's the problem, I recently restored a bunch of new rows to <code>items_archive</code> from old text data.  This old text data did NOT have the original id's, so I started just counting negatively so as to not affect the existing id's in either table.  However, not upon inspection I can see that things actually line up quite perfectly and I want to replace the negative id's with their proper ones.</p>
  
  <p>Here is what my current query and data looks like - </p>
  
  <pre><code>SELECT * FROM (
  SELECT idx,t_stamp, 'items' as src FROM items
  UNION
  SELECT idx,t_stamp, 'archive' as src FROM items_archive
  ) as t
  ORDER BY t.t_stamp DESC;
  </code></pre>
  
  <p>yields a table like this </p>
  
  <pre><code>id         t_stamp          src
  ==================================
  1878    2018-09-08 09:54:40 pallets
  1877    2018-09-08 09:02:58 pallets
  1876    2018-09-07 16:07:45 pallets
  -808    2018-09-06 09:53:39 archive
  -801    2018-09-05 13:43:52 archive
  1873    2018-09-05 13:00:32 archive
  -800    2018-09-05 11:48:55 archive
  1871    2018-09-05 11:05:13 archive
  </code></pre>
  
  <p>It's obvious that -808 should be 1875, -801 should be 1874, and -800 should be 1872.  How can I achieve that at a mass scale?</p>
  
  <p>I was thinking maybe making another temporary table that has all the id's from min to max, along with the timestamps in order based on the table I just showed you.  Then I could join on the time stamps and the simply update the rows in my archive table.  I am able to make the temporary table with the t_stamps, but I am at a loss of how to order the id's now in the new column from max to min, in order of the timestamp.  How can I do that?</p>
  
  <p>Or, if there's another way you can think of, please let me know.  My extensive manual observation of this tiny table (~2k rows) shows that the negatives fit into place perfectly.</p>
  
  <p>Using MySQL 5.6.</p>