mysql update a column based on nearest value of other column
<p>i have a table like this:</p>

<pre><code>CREATE TABLE IF NOT EXISTS `exam_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_code` int(11) NOT NULL,
  `total_level` int(11) NOT NULL,
  `total_rank` int(11) NOT NULL,
  `total_level_2` int(11) NOT NULL,
  `total_rank_2` int(11) NOT NULL,
);
</code></pre>

<p><strong>total_level</strong> and <strong>total_rank</strong> and <strong>total_level_2</strong> have values but <strong>total_rank_2</strong> is empty</p>

<p>i want to select value of <strong>total_level_2</strong> and find closest one inside <strong>total_level</strong>,
then update <strong>total_rank_2</strong> with <strong>total_rank</strong> of found row</p>

<p>how can i do it in an a query?</p>

<hr>

<p><strong>update:</strong></p>

<pre><code>total_level    |    total_rank    |    total_level_2    |    total_rank_2
--------------------------------------------------------------------------
1500           |    100           |    2000             |    ?
--------------------------------------------------------------------------
1700           |    90            |    3500             |    ?
--------------------------------------------------------------------------
2000           |    70            |    1500             |    ?
</code></pre>

<p>for example the last row after update should be 100.
beacuse the closest value of <strong>total_level_2</strong> in last row is inside first row , so <strong>total_rank_2</strong> of last row will be <strong>total_rank</strong> of first row</p>

<p>the table after update:</p>

<pre><code>total_level    |    total_rank    |    total_level_2    |    total_rank_2
--------------------------------------------------------------------------
1500           |    100           |    2000             |    70
--------------------------------------------------------------------------
1700           |    90            |    3500             |    70
--------------------------------------------------------------------------
2000           |    70            |    1500             |    100
</code></pre>