SQL> desc t_order;

Name Type Nullable Default Comments

---------- ------------- -------- ------- --------

ORDER_ID INTEGER Y

ORDER_NAME VARCHAR2(100) Y

SQL> select * from t_order;

ORDER_ID ORDER_NAME

--------------------------------------- --------------------------------------------------------------------------------

1 订单1

2 订单2

3 订单3

4 订单4

5 订单5

6 订单6

7 订单7

8 订单8

9 订单9

10 订单10

10 rows selected

SQL>

SQL> update t_order t

2 set t.order_name = t.order_name || 'newnew'

3 where exists (select 1

4 from (select *

5 from (select td.order_id, td.order_name, rownum rn

6 from t_order td) td

7 where td.rn <= 5

8 ) newt

9 where t.order_id = newt.order_id

10 )

11 ;

5 rows updated

SQL> select * from t_order;

ORDER_ID ORDER_NAME

--------------------------------------- --------------------------------------------------------------------------------

1 订单1newnew

2 订单2newnew

3 订单3newnew

4 订单4newnew

5 订单5newnew

6 订单6

7 订单7

8 订单8

9 订单9

10 订单10

10 rows selected

SQL> rollback;

Rollback complete

SQL>

SQL> update t_order t

2 set t.order_name = t.order_name || 'newnew'

3 where exists (select 1

4 from (select *

5 from (select td.order_id, td.order_name, rownum rn

6 from t_order td) td

7 where td.rn between 6 and 10

8 ) newt

9 where t.order_id = newt.order_id

10 )

11 ;

5 rows updated

SQL> select * from t_order;

ORDER_ID ORDER_NAME

--------------------------------------- --------------------------------------------------------------------------------

1 订单1

2 订单2

3 订单3

4 订单4

5 订单5

6 订单6newnew

7 订单7newnew

8 订单8newnew

9 订单9newnew

10 订单10newnew

10 rows selected

ITPub博客

如何通过rownum对表的不同范围进行批量更新update

最新文章