sql query for re-ordering data based on sort order sequence

Question

tablename =  zones

  ID     sort_order  area_name
======== =========   =========
  1        1          aaaa
  3        2          bbbb
  5        3          cccc
  7        4          dddd
  8        5          eeeee

i need to add a new area_name

eg: ffff in place of sort_order = 3.

and re-arrange the remaining areas sort_orders accordingly as shown below

  ID     sort_order  area_name
======== =========   =========
  1        1          aaaa
  3        2          bbbb
  9        3          ffff
  5        4          cccc
  7        5          dddd
  8        6          eeeee

need sql query for this

Update 'cccc' with 'ffff' and add new row with 'eeee'. That's the logic. Now you need is one update query and one Insert query. Try it. — Chetan
– Chetan, Commented Jun 27, 2018 at 8:33

Radim Bača · Accepted Answer · 2018-06-27 08:41:57Z

1

Use UPDATE and INSERT. I'm assuming that ID is auto-generated. The UPDATE makes room for the new record in the sort_order sequence and then you can easily insert.

 UPDATE zones
 SET sort_order = sort_order + 1
 WHERE sort_order >= 3

 INSERT INTO zones(sort_order , area_name) 
 VALUES (3, 'ffff')

However, if the zones table is heavily inserted then you will experience a lot of blocking wait.

edited Jun 27, 2018 at 8:41

answered Jun 27, 2018 at 8:36

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai Matei · Accepted Answer · 2018-06-27 08:36:49Z

1

You need 2 queries to do that.. First of all you update the sort_orders of the existing entries and insert the new entry afterwards:

UPDATE zones SET sort_order=sort_order + 1 WHERE sort_order >= 3;
INSERT INTO zones VALUES (NULL, 3, 'ffff')

answered Jun 27, 2018 at 8:36

Mihai Matei

24.3k5 gold badges34 silver badges50 bronze badges

Collectives™ on Stack Overflow

sql query for re-ordering data based on sort order sequence

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related