Set unique value in column and if its not unique move other one down

Question

I have problem with this. What I need is to get unique values in column called order in my event table. How this thing should behave: for example I have table `event

| id | name    | order |
|----|---------|-------|
| 1  | walking | 1     |
| 2  | diving  | 2     |
| 3  | skating | 3     |

for example when I try to update skating order to 1 I will get

| id | name    | order |
|----|---------|-------|
| 3  | skating | 1     |
| 1  | walking | 2     |
| 2  | diving  | 3     |

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

Yii::$app->db->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"
    )->execute();

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

Id field didn't changed it was 3 before update and it is 3 after update. But order changed from 3 to 1 — Vladyslav Startsev, Dec 17 '16 at 11:59
so in other words you are saying the value for the order column whould be swapped between skating and walking? — e4c5, Dec 17 '16 at 12:01
it should behave like this 1) trying to update table 2) if the number, for example 1, in order field exist, then set order field of the updating row to 1, and change all order field of other rows that order field is 1 or less to `order -1` (move them down ) so I will get skating 1, walking 2, diving 3, someOtherThing 4, etc. — Vladyslav Startsev, Dec 17 '16 at 12:12
In that case, I think this is what you want http://stackoverflow.com/a/40624979/267540 — e4c5, Dec 17 '16 at 12:14

score 1 · Answer 1 · answered Dec 17 '16 at 12:32

You don't need to regenerate all the orders using a user variable, since you're just adding 1 to them, except for the element that moves into position 1. So you can do it with IF or CASE.

UPDATE yourTable
SET `order` = CASE name
    WHEN 'skating' THEN 1
    ELSE `order` + 1
END

If you need to be able to move the element to some place other than 1, then it gets a little more complicated:

UPDATE yourTable
SET `order` = CASE
    WHEN name = 'skating' THEN @newpos
    WHEN `order` >= @newpos THEN `order` + 1
END

Set unique value in column and if its not unique move other one down

1 Answers1