Início / dba / Perguntas / 194903
Accepted
ReynierPM
Asked: 2018-01-09 07:51:14 +0800 CST

Como atualizar o valor da coluna adicionando "1" com base em uma condição?

  • 772

Tenho a seguinte tabela 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             |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+

Como você pode ver, existem dois field_seqcom o mesmo valor 36neste caso.

Digamos que eu esteja inserindo uma nova linha logo depois field_id=281960e a field_seqpara essa nova linha vem como 36.

Eu preciso construir uma consulta ou até mesmo um procedimento armazenado onde eu possa descobrir se existe uma linha com field_seqigual ou maior que 36e se sim, atualize o valor de field_seqpara o valor atual mais 1.

Ex:

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

Tendo isso veja os possíveis casos abaixo (com exemplos após cada um):

Caso 1: linha com field_seq=36 já existe na tabela

  • Mantenha os dados INSERT como eles se tornarão a field_seq=36nova linha atual
  • Atualize o valor da linha da tabela field_seq=current+1que se tornará37
  • Se já houver 37, repita o passo anterior até que não haja mais repetiçõesfield_seq

Antes da:

+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
| 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             |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+

Depois:

+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
| 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
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+

Caso 2: linha com field_seq=36 já existe na tabela, mas next field_seqé maior que37

  • Mantenha os dados INSERT como eles se tornarão a field_seq=36nova linha atual
  • Atualize o valor da linha da tabela field_seq=current+1que se tornará37
  • Neste caso, não precisamos continuar atualizando, pois há espaço suficiente para inserir mais algumas linhas antes que elas se tornem as mesmasfield_seq

Antes da:

+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| 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        |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+

Depois:

+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| 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
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+

Estou usando o Microsoft SQL Server 2016 (SP1). Como posso conseguir isso?

sql-server sql-server-2016

3 respostas

  1. Best Answer

    Você pode tentar isso:

    --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

    O código verificará se há uma colisão em field_seq.Se houver, ele examinará a tabela para encontrar a próxima lacuna, atualizará todos os field_seqvalores nesse intervalo e deixará uma lacuna para inserir o novo registro. Se nenhuma colisão for encontrada, a atualização será ignorada. Eu não posso fazer qualquer garantia sobre o desempenho para isso, no entanto. Tenho certeza de que existem maneiras mais ideais de fazê-lo.

    Aqui está o dbfiddle - você pode ver o antes e depois da atualização, criando uma lacuna para que a inserção ocorra.

    • 4

  2. Lembre-se de adicionar um manipulador de erros e usar uma TRANSACTION para garantir que todos os registros afetados sejam atualizados.

    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 linhas afetadas

    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

    ✓

    Insira um novo registro apósfield_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

    Insira um novo registro no final.

    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 aqui

    • 2

  3. Você sempre pode usar uma subconsulta em sua inserção, algo como ....

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

    pode funcionar

    • 1

relate perguntas