How to update column value by adding "1" based on a condition?

I have the following table fields:

```
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_name | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+
| 220481 | 9926 | NULL | 0 | NULL | NULL | 4 | 28 | Test | NULL | NULL |
| 281863 | 9926 | NULL | 0 | NULL | NULL | 10 | 29 | insert after yes no question | NULL | NULL |
| 220496 | 9926 | NULL | 0 | 11 | 1 | 5 | 30 | test | NULL | NULL |
| 249234 | 9926 | NULL | 0 | 12 | 1 | 5 | 32 | | NULL | NULL |
| 279877 | 9926 | NULL | 0 | NULL | NULL | 4 | 33 | Test Text Questions | NULL | NULL |
| 281860 | 9926 | NULL | 0 | NULL | NULL | 10 | 34 | Something | NULL | NULL |
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 35 | sssss | NULL | NULL |
| 281960 | 9926 | NULL | 0 | 38 | 1 | 5 | 36 | yuyuyu | NULL | NULL |
| 281972 | 9926 | NULL | 0 | 40 | 1

You could give this a try:

--enter procedure with insert parameters
DECLARE @field_seq INT = 36
DECLARE @field_seq_range INT

IF EXISTS(SELECT * FROM fields WHERE field_seq = @field_seq)
  BEGIN
    SELECT  @field_seq_range = MIN(f.field_seq)
    FROM    (
        SELECT  field_seq, LEAD(field_seq, 1, NULL) OVER (ORDER BY field_seq) next_field_seq
        FROM    fields
    ) f
    WHERE   f.field_seq >= @field_seq
    AND f.field_seq + 1 < f.next_field_seq

    UPDATE  f
    SET f.field_seq = f.field_seq + 1
    FROM    fields f
    WHERE   f.field_seq BETWEEN @field_seq AND @field_seq_range
  END

--perform insert

The code is going to check to see if there is a collision on field_seq. If there is, it will scan the table to find the next gap, update all of the field_seq values in that range, and leave you a gap to insert the new record into. If no collision is found, the update is skipped. I can't make any guarantee on performance for this, though. I'm sure there are more optimal ways to do it.

Here's the dbfiddle - you can see the before and after the update occurs creating a gap for the insert to take place.

StackExchange Database Administrators Q#194903, answer score: 4