1

I have a table with a custom sort order column.

ID    NAME    ORDER
1     Jack    4
2     Jill    2
3     Mike    5
4     Mark    1
5     Bill    3

I would like to insert a new record with an ID of 6, a NAME of Jane, and an ORDER of 3. I would like to insert it such that the old records are incremented to make room for the new record, resulting in something like this:

ID    NAME    ORDER
1     Jack    5
2     Jill    2
3     Mike    6
4     Mark    1
5     Bill    4
6     Jane    3

Can this be done using a SQL script? I was looking at this answer, but I'm not sure it can be made to work in my case. Plus it requires an extra table to temporarily hold values, which I would like to avoid.

Thanks.

[edit]

I forgot to add that there is a unique constraint non the third column. Though I understand there are ways of getting around this.

GMB
  • 216,147
  • 25
  • 84
  • 135
posfan12
  • 2,541
  • 8
  • 35
  • 57

2 Answers2

1

You can do this with two queries:

update mytable set order = order + 1 where order >= 3;
insert into mytable(id, name, order) values(6, 'Jane', 3);

Note, however, that this create a race condition, and might not behave properly under concurency stress.

A better solution would be not to store the customer ordering, but compute it on the fly in your queries (you can create a view to make it easier). For this, you would need to describe the logic behind the ordering.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Yeah, I would normally use a script to calculate the ordering inside or outside of MySQL. But the ordering in this case is mostly arbitrary with no pattern. – posfan12 Dec 14 '19 at 10:53
  • When I run your script I get error 1062 and complaints about duplicate values. I have tried disabling unique checks using `SET UNIQUE_CHECKS=0;` but without success. Not sure what else to try. – posfan12 Dec 14 '19 at 13:01
  • Is there some way to set the `order` in reverse? I.e. larger numbers first, then smaller numbers? – posfan12 Dec 14 '19 at 13:06
  • 1
    I simply needed to tack on `ORDER BY order DESC` to the end of your update statement. Now it works, thanks. – posfan12 Dec 14 '19 at 13:44
1

Try this

INSERT INTO mytable(id, name, order) values (6, 'Jane', 0);
UPDATE mytable SET order = CASE WHEN id % 2 <> 0 THEN order + 1 ELSE order / 2;
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115