Sql-server – How to update column value by adding “1” based on a condition

sql serversql-server-2016update

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            |             5 |        40 | ttttt                        | NULL        | NULL             |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+

As you can see there are two field_seq with the same value 36 in this case.

Let's say I am inserting a new row just after field_id=281960 and the field_seq for such new row is coming as 36.

I need to build a query or even a stored procedure where I am able to find out if there is a row with field_seq equal or greater than 36 and if so then update the value of field_seq to the current value plus 1.

Ex:

INSERT INTO `fields` VALUES(9999, 9926, NULL, 0, 41, 1, 5, 36, 'lllll', NULL, NULL);

Having this see possible cases below (with examples after each one):

Case 1: row with field_seq=36 exists on table already

  • Keep the INSERT data as it's which will become the current field_seq=36 new row
  • Update the value of table row field_seq=current+1 which will become 37
  • If there is a 37 already then repeat the previous step until there is no more repeated field_seq

Before:

+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_nanme | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
|   281914 |    9926 | NULL            |           0 |      23 |            1 |             5 |        32 | sssss       | NULL        | NULL             |
|   281972 |    9926 | NULL            |           0 |      40 |            1 |             5 |        36 | ttttt       | NULL        | NULL             |
|   281960 |    9926 | NULL            |           0 |      38 |            1 |             5 |        37 | yuyuyu      | NULL        | NULL             |
|   281978 |    9926 | NULL            |           0 |      38 |            1 |             5 |        38 | vvvvv       | NULL        | NULL             |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+

After:

+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq |     field_nanme     | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
|   281914 |    9926 | NULL            |           0 | 23      | 1            |             5 |        32 | sssss               | NULL        | NULL             |
|     9999 |    9926 | NULL            |           0 |    41   |   1          |             5 |        36 | lllll               | NULL        | NULL             | => new row inserted here
|   281972 |    9926 | NULL            |           0 | 40      | 1            |             5 |        37 | ttttt               | NULL        | NULL             | => this was 36 now is updated to 37
|   281960 |    9926 | NULL            |           0 | 38      | 1            |             5 |        38 | yuyuyu              | NULL        | NULL             | => this was 37 now is updated to 38
|   281978 |    9926 | NULL            |           0 | 38      | 1            |             5 |        39 | vvvvv               | NULL        | NULL             | => this was 38 now is updated to 39
|   220524 |    9926 | NULL            |           0 | NULL    | NULL         |             5 |        40 | Patient Information | NULL        | NULL             | => we don't care about this cause there is room for one more, if one insert makes the rows above become 40 then this needs to be updated to 41
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+

Case 2: row with field_seq=36 exists on table already but next field_seq is greater than 37

  • Keep the INSERT data as it's which will become the current field_seq=36 new row
  • Update the value of table row field_seq=current+1 which will become 37
  • In this case we do not need to continue updating since there is enough space to insert a few more rows before them become the same field_seq

Before:

+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid  | field_type_id | field_seq | field_name | field_class |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
|   281914 |    9926 | NULL            |           0 |      23 |             1 |             5 |        32 | sssss      | NULL        |
|   281972 |    9926 | NULL            |           0 |      40 |             1 |             5 |        36 | ttttt      | NULL        |
|   281972 |    9926 | NULL            |           0 |      40 |             1 |             5 |        40 | ooooo      | NULL        |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+

After:

+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid  | field_type_id | field_seq | field_name | field_class |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
|   281914 |    9926 | NULL            |           0 |      23 |             1 |             5 |        32 | sssss      | NULL        |
|   281972 |    9926 | NULL            |           0 |      41 |             1 |             5 |        36 | lllll      | NULL        | => new row inserted here
|   281972 |    9926 | NULL            |           0 |      40 |             1 |             5 |        37 | ttttt      | NULL        | => previous row with field_seq=36 was updated to 37
|   281972 |    9926 | NULL            |           0 |      40 |             1 |             5 |        40 | ooooo      | NULL        | => nothing happen to this one since there is room for more
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+

I am using Microsoft SQL Server 2016 (SP1). How I can achieve this?

Best Answer

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.

Related Question