I have a table like this :
id name family order
861 a a 1
5241 b b 2
15 c c 3
251 d d 4
.
.
.
541 e e 9001
41 f f 9002
8161 g g 9003
When I want get my data I use this query :
SELECT * FROM users ORDER BY order ASC;
But also I need insert one item between two items with id 861 and 5241. So in this way I need change all orders of all rows.
UPDATE users SET order=9004 WHERE order=9003;
UPDATE users SET order=9003 WHERE order=9002;
UPDATE users SET order=9002 WHERE order=9001;
.
.
.
UPDATE users SET order=4 WHERE order=3;
UPDATE users SET order=3 WHERE order=2;
INSERT INTO users (name, family, order) VALUES ('z', 'z', 2);
And we know it is very bad idea. Is there a better way?
CodePudding user response：
Something like this:
START TRANSACTION;
UPDATE users SET order_c = order_c 1 where order_c >= 2;
insert into users values(123, 'f', 'f', 2);
COMMIT;
I think the exact was recommended by @EdmCoff ?
CodePudding user response：
Given
select * from t;
------------ ----------
| dte | order_id |
------------ ----------
| 2018-01-02 | 1 |
| 2018-01-03 | 2 |
| 2018-01-04 | 3 |
| 2018-01-05 | 4 |
| 2018-01-06 | 5 |
| 2018-01-07 | 6 |
| 2018-01-08 | 7 |
| 2018-01-09 | 8 |
| 2018-01-10 | 9 |
| 2018-01-11 | 10 |
------------ ----------
If you want to code and forget consider
drop trigger if exists t;
delimiter $$
create trigger t after insert on t
for each row
begin
insert into t1 values (new.dte,new.order_id,null,0);
end $$
delimiter ;
create table t1 like t;
alter table t1
add column AI_order_id int auto_increment primary key,
add column new_order_id int;
alter table t1
add unique key (order_id,AI_order_id);
truncate table t1;
insert into t1 select dte,order_id, null,0 from t; #bulk load
#select * from t1;
insert into t values (date('2023-05-01'),2),(date('2024-05-01'),2),(date('2024-05-01'),10),(date('2024-05-01'),1);
update t1 join
(select order_id,AI_order_id, row_number() over(order by order_id,AI_order_id desc) rn from t1) s
on t1.order_id = s.order_id and t1.AI_order_id = s.AI_order_id
set new_order_id = s.rn
where 1 = 1;
update t join t1 on t1.order_id = t.order_id and t1.dte = t.dte
set t.order_id = t1.new_order_id
where 1 = 1;
select * from t1 order by order_id,new_order_id;
------------ ---------- ------------- --------------
| dte | order_id | AI_order_id | new_order_id |
------------ ---------- ------------- --------------
| 2024-05-01 | 1 | 19 | 1 |
| 2018-01-02 | 1 | 1 | 2 |
| 2024-05-01 | 2 | 17 | 3 |
| 2023-05-01 | 2 | 16 | 4 |
| 2018-01-03 | 2 | 2 | 5 |
| 2018-01-04 | 3 | 3 | 6 |
| 2018-01-05 | 4 | 4 | 7 |
| 2018-01-06 | 5 | 5 | 8 |
| 2018-01-07 | 6 | 6 | 9 |
| 2018-01-08 | 7 | 7 | 10 |
| 2018-01-09 | 8 | 8 | 11 |
| 2018-01-10 | 9 | 9 | 12 |
| 2024-05-01 | 10 | 18 | 13 |
| 2018-01-11 | 10 | 10 | 14 |
------------ ---------- ------------- --------------
14 rows in set (0.001 sec)
select * from t order by order_id;
------------ ----------
| dte | order_id |
------------ ----------
| 2024-05-01 | 1 |
| 2018-01-02 | 2 |
| 2024-05-01 | 3 |
| 2023-05-01 | 4 |
| 2018-01-03 | 5 |
| 2018-01-04 | 6 |
| 2018-01-05 | 7 |
| 2018-01-06 | 8 |
| 2018-01-07 | 9 |
| 2018-01-08 | 10 |
| 2018-01-09 | 11 |
| 2018-01-10 | 12 |
| 2024-05-01 | 13 |
| 2018-01-11 | 14 |
------------ ----------
14 rows in set (0.001 sec)