4

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?

  • Are you specifically looking for a value of 36 for inserted rows OR are you saying you want to check for an existing field_seq value that is greater than ANY incoming row due to an insert? What if there is already a row with field_seq of 37 and a 36 is being inserted? What if there are multiple rows being inserted into the table in one transaction? I think more clarification on the criteria and desired results is needed. – Scott Hodgin Jan 8 '18 at 16:15
  • 2
    I only see one record with field_seq equal to 36 in your first table. – mathewb Jan 8 '18 at 16:16
  • @mathewb 36 is jut an example based on the preview data I provided it could be any value the rules I want to apply are the ones that should be matched no matter what the value of field_seq is. If there is a field_seq=37 and 36 is being inserted then I should look for existent 36 in DB, if does not exists then 37 remains the same but if does exists then 37 becomes 38, is that clear? – ReynierPM Jan 8 '18 at 16:18
  • 1
    The question is much clearer now. Next question - what version of SQL Server are you working in? – mathewb Jan 8 '18 at 16:34
  • 1
    @mathewb I have edited the OP again to reflect the two cases I am seeing here and added the SQL version as well, please check and let me know – ReynierPM Jan 8 '18 at 16:49
4

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.

2

Keep in mind you should add an error handler and use a TRANSACTION to be sure all affected records become updated.

CREATE TABLE T(field_id int, field_seq int);

INSERT INTO T VALUES 
(22156, 28),
(22759, 29),
(23458, 30),
(28000, 31),
(28101, 32),
(29355, 33),
(30000, 34),
(30125, 35);
GO
8 rows affected
CREATE PROCEDURE InsertNewID(@new_id int)
AS
BEGIN

    DECLARE @field_seq int = 0,
            @field_id int = 0;

    -- try to find if there is some field_id > @new_id
    SELECT   TOP 1 
             @field_id = COALESCE(field_id, 0),
             @field_seq = COALESCE(field_seq, 0)
    FROM     T
    WHERE    field_id > @new_id
    ORDER BY field_id ASC;

    -- if there isn't any field_id > @new_id
    -- get MAX(field_seq) OR 1 in case there is no records
    IF @field_seq = 0
    BEGIN
        SELECT @field_seq = COALESCE(MAX(field_seq), 0) + 1
        FROM   T
    END

    IF @field_id > 0
    BEGIN
        UPDATE T
        SET    field_seq = field_seq + 1
        WHERE  field_id >= @field_id
    END

    INSERT INTO T (field_id, field_seq) VALUES (@new_id, @field_seq);
END
GO

Insert a new record after field_id=29355

EXEC InsertNewId @new_id = 29999;

SELECT * FROM T ORDER BY field_id;
GO
field_id | field_seq
-------: | --------:
   22156 |        28
   22759 |        29
   23458 |        30
   28000 |        31
   28101 |        32
   29355 |        33
   29999 |        34
   30000 |        35
   30125 |        36

Insert new record at end.

EXEC InsertNewId @new_id = 31000;

SELECT * FROM T ORDER BY field_id;
GO
field_id | field_seq
-------: | --------:
   22156 |        28
   22759 |        29
   23458 |        30
   28000 |        31
   28101 |        32
   29355 |        33
   29999 |        34
   30000 |        35
   30125 |        36
   31000 |        37

dbfiddle here

  • Can you check the updated OP? Did your SP cover the 2nd case? – ReynierPM Jan 8 '18 at 16:59
1

You can always use a subquery in your insert, something like....

INSERT INTO `fields` VALUES(9999, 9926, NULL, 0, 41, 1, 5, (SELECT MAX(field_seq) + 1 FROM fields), 'lllll', NULL, NULL);

might work

  • I think that the OP wants to update the contents of the table and keep the insert as it is. It should behave sort of like a linked list. – mathewb Jan 8 '18 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.