Set unique value in column and if its not unique move other one down
<p>I have problem with this. What I need is to get unique values in column called <code>order</code> in my <code>event</code> table.
How this thing should behave: for example I have table `event</p>

<pre><code>| id | name    | order |
|----|---------|-------|
| 1  | walking | 1     |
| 2  | diving  | 2     |
| 3  | skating | 3     |
</code></pre>

<p>for example when I try to update <code>skating</code> order to <code>1</code>  I will get </p>

<pre><code>| id | name    | order |
|----|---------|-------|
| 3  | skating | 1     |
| 1  | walking | 2     |
| 2  | diving  | 3     |
</code></pre>

<p>Have no idea how to do it, tried this but it doesn't work for me:</p>

<pre><code>Yii::$app-&gt;db-&gt;createCommand("
        SET @rownum :=0;
        UPDATE  " . self::tableName() . "
        LEFT JOIN (
            SELECT id,  (@rownum := @rownum + 1) AS rowNumber
            FROM " . self::tableName() . "
            ORDER BY order ASC
        ) as tmp_table
        on tmp_table.id = " . self::tableName() . ".id
        SET " . self::tableName() . ".order = tmp_table.rowNumber"
    )-&gt;execute();
</code></pre>

<p>If it's not understandable for you, ask me I will try to explain all the things.</p>