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_seq
com o mesmo valor 36
neste caso.
Digamos que eu esteja inserindo uma nova linha logo depois field_id=281960
e a field_seq
para 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_seq
igual ou maior que 36
e se sim, atualize o valor de field_seq
para 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=36
nova linha atual - Atualize o valor da linha da tabela
field_seq=current+1
que 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=36
nova linha atual - Atualize o valor da linha da tabela
field_seq=current+1
que 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 mesmas
field_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?
Você pode tentar isso:
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 osfield_seq
valores 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.
Lembre-se de adicionar um manipulador de erros e usar uma TRANSACTION para garantir que todos os registros afetados sejam atualizados.
Insira um novo registro após
field_id=29355
Insira um novo registro no final.
dbfiddle aqui
Você sempre pode usar uma subconsulta em sua inserção, algo como ....
pode funcionar