在保持id连续性的同时从数据库中删除行

Question

我有一个10行的表，ids为1-10。我希望删除第5行和第8行，并希望更新I，使其为1-8，而不是1-4、6-7和9-10。我不想运行大量的update语句，也不想手工操作。此外，我还需要对SQLServer和Oracle数据库都适用的解决方案。我认为像程序这样的东西可以做到，但我不知道如何创建一个程序，也不知道如何实现它。

Answer 1

您应该包括您尝试过的内容，无论如何，您可以运行此查询，直到不再发生更新(如果您不关心性能，那么按id进行更好的排序，并像其他答案一样使用行号)：

UPDATE myTable t SET id = id - 1 WHERE id <> 1 AND
   NOT EXISTS (SELECT * FROM myTable s WHERE s.id = t.id - 1)

Answer 2

正如戈登·林诺夫所指出的那样，你真的不应该这样做。如果你还想这么做，下面是一个UPDATE

update t_table t set id = (
    select newid from (
        select id, row_number() over (order by id) newid from t_table
    ) x
    where x.id = t.id
)

只要允许更新主键，语法可能在Oracle和MSSQL (仅在Oracle中测试)中都能使用。尽管如此，我还是建议不要使用这种技术。如果您想要一个连续的整数列，请使用row_number()，因为在上面的查询中使用它动态生成一个整数列。

Answer 3

在Oracle中，这很容易--您只需使用MERGE语句以及row_number()解析函数，如下所示：

drop table test1;

create table test1
as
select level id,
       level some_val
from   dual
connect by level <= 10;

delete from test1
where id in (5, 8);

merge into test1 tgt
using (select rowid row_id,
              id,
              row_number() over (order by id) rn
       from   test1) src
  on (tgt.rowid = src.row_id)
when matched then
update set tgt.id = src.rn;

commit;

select * from test1;

        ID   SOME_VAL
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          6
         6          7
         7          9
         8         10

ETA:我会重复其他人关于为什么你需要这样做的话--尤其是如果id列是主键，并且有外键引用它的话！