将行数传递到Oracle中的列 [英] Pass row count to a column in Oracle

问题描述

我有一个没有ID的表.所以我想添加一个新列，该列总数为1.我在表中打开了一个新列，但我不知道如何用行数填充它.我在Internet上搜索，但是找不到解决我问题的解决方案.你能告诉我怎么做吗?预先感谢.

I have a table which doesn't have an ID. So I want to add a new column which is 1 to total row number. I opened a new column in the table but I don't know how to fill it with row count. I searched on the Internet but I couldn't find a solution that solves my problem. Can you tell me how to do this? Thanks in advance.

推荐答案

最简单的解决方案是

update your_table
set id = rownum;

但是，这对以后插入此表不会有帮助.如果那会发生，请构建一个序列.

However this won't help you with future insertions to this table. If that is something with is going to happen build a sequence.

SQL> select max(id) from your_table;

   MAX(ID)
----------


SQL> create sequence your_table_seq;

Sequence created.

SQL> update your_table
  2  set id = your_table_seq.nextval;

30 rows updated.

SQL> select max(id) from your_table;

   MAX(ID)
----------
        30

SQL> select id from your_table;

        ID
----------
         1
         2
         3
....
        28
        29
        30

30 rows selected.

SQL> 

---

顺便说一句，现在您已经添加了一个ID列并填充了它，请确保将其强制为主键:

---

By the way, now you've added an ID column and populated it be sure to enforce it as a primary key:

alter table your_table add constraint your_table_pk primary key (id);

这篇关于将行数传递到Oracle中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！