在Oracle中使用序列号更新整个表 [英] Update entire table with sequence number in oracle
本文介绍了在Oracle中使用序列号更新整个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想用序列号更新表,我无法创建过程或序列对象,因此需要对此进行更新查询.我有一个带有日期列的表格,该表格基于我需要生成数字的最小日期.
I want to update table with sequence number, I cant create procedure or sequence object so need a update query for this. I have a table with date column, based on minimum date I need to generate a number.
表格数据:-
Date_Value
----------
5th Feb 11
2nd Jan 11
11th Jan 11
After Update :-
SrNo Date_Value
-------------------
1 2nd Jan 11
2 11th Jan 11
3 5th Feb 11
推荐答案
merge into foo
using
(
select rowid as rid,
row_number() over (order by date_value) as seqno
from foo
) t on (foo.rowid = t.rid)
when matched then update
set srno = t.seqno;
SQLFiddle演示: http://sqlfiddle.com/#!4/d8cc5/2
SQLFiddle demo: http://sqlfiddle.com/#!4/d8cc5/2
这篇关于在Oracle中使用序列号更新整个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文