我想用一个整数填充一个表列,所以我在考虑使用ROWNUM。但是,我需要根据其他列的顺序来填充它,比如ORDER BY column1, column2
。不幸的是,这是不可能的,因为Oracle不接受以下语句:
UPDATE table_a SET sequence_column = rownum ORDER BY column1, column2;
也不是以下语句(尝试使用WITH子句):
WITH tmp AS (SELECT * FROM table_a ORDER BY column1, column2)
UPDATE tmp SET sequence_column = rownum;
那么,如何在PL/SQL中使用SQL语句而不借助游标迭代方法来完成此操作呢?
发布于 2011-05-23 15:21:39
这应该行得通(对我也行)
update table_a outer
set sequence_column = (
select rnum from (
-- evaluate row_number() for all rows ordered by your columns
-- BEFORE updating those values into table_a
select id, row_number() over (order by column1, column2) rnum
from table_a) inner
-- join on the primary key to be sure you'll only get one value
-- for rnum
where inner.id = outer.id);
或者使用MERGE
语句。就像这样。
merge into table_a u
using (
select id, row_number() over (order by column1, column2) rnum
from table_a
) s
on (u.id = s.id)
when matched then update set u.sequence_column = s.rnum
发布于 2011-05-23 15:36:58
UPDATE table_a
SET sequence_column = (select rn
from (
select rowid,
row_number() over (order by col1, col2)
from table_a
) x
where x.rowid = table_a.rowid)
但这不会很快,正如Damien指出的那样,每次更改该表中的数据时,您都必须重新运行该语句。
发布于 2014-02-03 12:26:25
首先创建一个序列:
CREATE SEQUENCE SEQ_SLNO
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
之后,使用以下序列更新表:
UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;
https://stackoverflow.com/questions/6094039
复制相似问题