Re: How to accomplish this using ONLY one update sentence?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 22 Jan 2006 13:21:33 +0100
Message-ID: <43d378cc$0$7382$626a14ce@news.free.fr>

"kent" <dashen.zhang_at_johnsondiversey.com> a écrit dans le message de news: dqvrhr$ve$1_at_news.cn99.com...
| There is a table named test1.
| it have 2 number fields: field1 field2
| the initial value is:
| field1 field2
| 1 null
| 1 null
| 2 null
| 3 null
| 3 null
| 3 null
|
| how to accomplish this using ONLY one update sentence?
| Group by field1, that is field1 =1 is one group, field2 =2 is another group
| use field2 to represent the sequence in the group
|
| the result is :
|
| field1 field2
| 1 1
| 1 2
| 2 1
| 3 1
| 3 2
| 3 3
|
|

SQL> select * from t;

    FIELD1 FIELD2
---------- ----------

         1
         1
         2
         3
         3
         3

6 rows selected.

SQL> update t a
  2 set field2 = (select b.rn

  3                from (select rowid rid,
  4                             row_number() over(partition by field1 order by field2) rn
  5                      from t) b
  6                where a.rowid = b.rid)

  7 /

6 rows updated.

SQL> select * from t;

    FIELD1 FIELD2
---------- ----------

         1          1
         1          2
         2          1
         3          1
         3          2
         3          3

6 rows selected.

Regards
Michel Cadot Received on Sun Jan 22 2006 - 06:21:33 CST