0

I have participant table in mysql. For example the table data is like this enter image description here

For example, there is a mistake. Ignit must be in 4th position. If I swap Donald to Ignit, it will become like this which is wrong :

enter image description here

So, I'm going to swap one by one

Ignit -> Harry, Ignit->Gerrard, Ignit->Funky, Ignit->Emma, Ignit->Donald so it will become :
1. Andy
2. Barry
3. Charlie
4. Ignit
5. Donald
6. Emma
7. Funky
8. Gerrard
9. Harry
10. John

The problem is, how can I swap if there are thousand of participants ? It's cumbersome to swap one by one and it takes long time.

Making changes to multiple records based on change of single record with SQL Doesn't answer my question. I want more efficient way (If there any).

Community
  • 1
  • 1
Zainab Safiyyah Al-habsyi
  • 79
  • 8
  • Possible duplicate of [Making changes to multiple records based on change of single record with SQL](http://stackoverflow.com/questions/40623132/making-changes-to-multiple-records-based-on-change-of-single-record-with-sql) – e4c5 Feb 13 '17 at 06:41
  • surely not the answer your looking for but one of my old question can lead you on the right track. => http://stackoverflow.com/questions/30579523/mysql-incrementing-variable-performance – Louis Loudog Trottier Feb 13 '17 at 06:41
  • how do you determine- ignit must be at 4 is there any dimension for sorting, or just random one – Veshraj Joshi Feb 13 '17 at 06:47
  • The order participant_order is the order of their seats. – Zainab Safiyyah Al-habsyi Feb 13 '17 at 06:50

1 Answers1

2

This may look unpleasant but hope it will help you.

set @currentorder=9;
set @correctorder=4;
set @participant_id=9;
update participants set participant_order=participant_order+1 where participant_order between @correctorder and @currentorder-1;
update participants set participant_order=@correctorder where participant_id=@participant_id;
Camahalan Royette
  • 166
  • 1
  • 12