请问一下,根据rownum来更新某一字段值这个怎么实现?

[beysion]

Table1
ID Name Ordering
1 xx 5
2 yy 6

我想根据Ordering的升序取得rownum来更新ordering
更新之后

ID Name Ordering
1 xx 1
2 yy 2

请教高手,这个Sql怎么写啊?
非常感谢,在线等...

[hongqi162]

updatea table set ordering=rownum

[hongqi162]

update table1 t1 set ordering=
(select num from
(select t.*,rownum num from table1 t) t2 where t2.id=t1.id)

[TodayZ]

update Table1 t1 set Ordering = (select count(*) from Table1 t2 where t2.Ordering <= t1.Ordering)

[wwwzjb]

楼上的有问题,你可以先查询出来放入一个临时表,以后的会做了吧
select rwnum sn a.* from (select * from Table1 order by ordering asc) a

[临摹]

UPDATE TABLE1 T1 SET Ordering=
(SELECT RN FROM
(SELECT ROWNUM RN,T.* FROM (SELECT * FROM TABLE1 ORDER BY Ordering)T)T2
WHERE T1.ID=T2.ID);

[beysion]

好象不行啊,你们那里可以了吗?
谁的可以了啊?

[临摹]

CREATE TABLE Table1(ID INTEGER,Name VARCHAR2(10),Ordering INTEGER);
INSERT INTO TABLE1 VALUES(1,'xx',5);
INSERT INTO TABLE1 VALUES(2,'yy',6);
INSERT INTO TABLE1 VALUES(3,'yy',3);
INSERT INTO TABLE1 VALUES(4,'yy',7);
INSERT INTO TABLE1 VALUES(5,'yy',1);
COMMIT;
SQL> select * from table1 order by Ordering;

ID NAME ORDERING
---------- ---------- ----------
5 yy 1
3 yy 3
1 xx 5
2 yy 6
4 yy 7

SQL> UPDATE TABLE1 T1 SET Ordering=
2 (SELECT RN FROM
3 (SELECT ROWNUM RN,T.* FROM (SELECT * FROM TABLE1 ORDER BY Ordering)T)T2
4 WHERE T1.ID=T2.ID);

已更新5行。

SQL> select * from TABLE1;

ID NAME ORDERING
---------- ---------- ----------
1 xx 3
2 yy 4
3 yy 2
4 yy 5
5 yy 1

SQL> select * from table1 order by Ordering;

ID NAME ORDERING
---------- ---------- ----------
5 yy 1
3 yy 2
1 xx 3
2 yy 4
4 yy 5

不是这个意思吗？

[beysion]

是你这个意思,我这里怎么不行,郁闷...

[beysion]

就停在那里了,一直处于Excuing....