Oracle:使用ROWNUM和ORDER BY子句一起更新表列 [英] Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
问题描述
我想用一个运行中的整数填充一个表列,所以我正在考虑使用ROWNUM.但是,我需要根据其他列的顺序填充它,例如ORDER BY column1, column2
.不幸的是,这是不可能的,因为Oracle不接受以下语句:
I want to populate a table column with a running integer number, so I'm thinking of using ROWNUM. However, I need to populate it based on the order of other columns, something like ORDER BY column1, column2
. That is, unfortunately, not possible since Oracle does not accept the following statement:
UPDATE table_a SET sequence_column = rownum ORDER BY column1, column2;
也不执行以下语句(尝试使用WITH子句):
Nor the following statement (an attempt to use WITH clause):
WITH tmp AS (SELECT * FROM table_a ORDER BY column1, column2)
UPDATE tmp SET sequence_column = rownum;
那么我该如何使用SQL语句而不在PL/SQL中使用游标迭代方法呢?
So how do I do it using an SQL statement and without resorting to cursor iteration method in PL/SQL?
推荐答案
这应该有效(对我有用)
This should work (works for me)
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
语句.像这样的东西.
OR you use the MERGE
statement. Something like this.
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
这篇关于Oracle:使用ROWNUM和ORDER BY子句一起更新表列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!