A表字段
A1 A2
0
10
20
50
88
120
200
320
.
.
用一个sql更新字段A2的值,把值改成下一行A1字段的值。 如下
字段
A1 A2
0 10
10 20
20 50
50 88
88 120
120 200
200 320
320
.
.
A1 A2
0
10
20
50
88
120
200
320
.
.
用一个sql更新字段A2的值,把值改成下一行A1字段的值。 如下
字段
A1 A2
0 10
10 20
20 50
50 88
88 120
120 200
200 320
320
.
.
with tb as(
select 0 A1 from dual union all
select 10 from dual union all
select 20 from dual union all
select 50 from dual union all
select 88 from dual union all
select 120 from dual union all
select 200 from dual union all
select 320 from dual)
select a1,lead(a1) over (order by a1)
from tb A1 LEAD(A1)OVER(ORDERBYA1)
---------- -----------------------
0 10
10 20
20 50
50 88
88 120
120 200
200 320
320
select a1,lead(a1) over (order by a1) a2 from a) b where b.a1=a.a1);
merge into tb
using (select a1,lead(a1) over (order by a1) a2
from tb) t
on (tb.a1=t.a1)
when matched then
update
set tb.a2=t.a2
2 / A1 A2
---------- ----------
0
10
20
50
88
120
200
320已选择8行。SQL> ed
已写入 file afiedt.buf 1 update tt03 a set a2=(select na2
2* from (select lead(a1,1) over(order by a1) na2 from tt03) b where a.rowid=b.rowid)
SQL> /已更新8行。SQL> commit
2 /提交完成。SQL> select * from tt03
2 / A1 A2
---------- ----------
0 10
10 20
20 50
50 88
88 120
120 200
200 320
320已选择8行。
A1 A2
---------- ----------
0
10
20
50
88
120
200
320
8 rows selected
SQL> update tablea a set a.a2=(select min(b.a1) from tablea b where b.a1>a.a1);
8 rows updated
SQL> select * from tablea;
A1 A2
---------- ----------
0 10
10 20
20 50
50 88
88 120
120 200
200 320
320
8 rows selected
SQL>