UPDATE 表a SET name='tom' WHERE ID IN (SELECT ID FROM( SELECT ID FROM 表a WHERE SEX='男' )A WHERE ROWNUM<=10) 功能是修改表a中性别为‘男’的前十条数据的name字段,以上这条语句是否正确?我在java或plsql中运行这条语句要等很长的时间(估计有2分钟)才能修改成功。有没有人有简单高效的方法??
不建议用rownum, 用row_number()UPDATE a SET name='tom' where id in ( select id from ( SELECT ID,row_number()(order by id) rn FROM a WHERE SEX='男' ) where rn<11)
UPDATE 表a t SET t.name='tom' WHERE ROWNUM<=10
UPDATE 表a t SET t.name='tom' WHERE sex='男' and ROWNUM<=10
SQL> SELECT * FROM test ;
A B --- ---- 14 0
SQL> update test t set t.a=11 where rownum<=10;
1 row updated
SQL> select * from test;
A B --- ---- 11 0
SQL>
-- 这样是错误的,会误导人的!别想的太简单了......UPDATE a SET a.name='tom' WHERE EXISTS (SELECT b.ID FROM ( SELECT ID FROM 表a WHERE SEX='男' ) b WHERE ROWNUM<=10 and b.ID=a.ID);
如果只是ROWNUM<=10是可以修改的,但前面再加上条件,就会报错了...
SQL> SELECT * FROM test ;
A B --- ---- 14 0
SQL> update test t set t.a=11 where rownum<=10;
1 row updated
SQL> select * from test;
A B --- ---- 11 0
SQL> update test t set t.a=11 where t.a=11 and rownum<=10;
0 rows updated
SQL> update test t set t.a=11 where t.a=14 and rownum<=10; SQL> update test t set t.a=11 where t.a=14 and rownum<=10;
是啊,不过楼主没说按什么排序,so我就给他个没排序的。 排序的话可以这么写 UPDATE 表a t SET t.name='tom' where rowid in (select rowid from (select id from 表a where sex='男' order by 排序字段)where rownum<=10 )
试试这个快不: UPDATE a t SET t.name='tom' where t.id in ( select id from ( select id,row_num() (partition by sex,order by id,name) rn from a where sex='男' and rn<=10) b)
UPDATE a t SET t.name='tom' where t.id in ( select id from ( select id,row_number() (partition by sex,order by id,name) rn from a where sex='男' and rn<=10) b)
前十条记录不排序的话,用UPDATE 表a t SET t.name='tom' WHERE sex='男' and ROWNUM<=10 修改没问题,不知道luoyoumou所说的是什么意思,等待他的模拟结果。
用row_number()UPDATE a SET name='tom'
where id in
(
select id
from
(
SELECT ID,row_number()(order by id) rn FROM a WHERE SEX='男'
)
where rn<11)
A B
--- ----
14 0
SQL> update test t set t.a=11 where rownum<=10;
1 row updated
SQL> select * from test;
A B
--- ----
11 0
SQL>
-- 这样是错误的,会误导人的!别想的太简单了......UPDATE a SET a.name='tom'
WHERE EXISTS (SELECT b.ID
FROM ( SELECT ID FROM 表a WHERE SEX='男' ) b
WHERE ROWNUM<=10 and b.ID=a.ID);
A B
--- ----
14 0
SQL> update test t set t.a=11 where rownum<=10;
1 row updated
SQL> select * from test;
A B
--- ----
11 0
SQL> update test t set t.a=11 where t.a=11 and rownum<=10;
0 rows updated
SQL> update test t set t.a=11 where t.a=14 and rownum<=10;
SQL> update test t set t.a=11 where t.a=14 and rownum<=10;
1 row updated
SQL> select * from test;
A B
--- ----
11 0
SQL>
-- 当然不会报错,但是更新的记录行可能不会有10行(当 sex='男' 的记录行超过10行的时候)
SQL> select * from test;
A B
--- ----
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
A B
--- ----
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
A B
--- ----
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
14 0
A B
--- ----
14 0
14 0
64 rows selected
SQL> update test t set t.a=11 where t.a=14 and rownum<=10;
10 rows updated
SQL> select a ,count(*) from test group by a;
A COUNT(*)
--- ----------
11 10
14 54
SQL>
排序的话可以这么写
UPDATE 表a t SET t.name='tom'
where rowid in (select rowid from (select id from 表a where sex='男' order by 排序字段)where rownum<=10 )
UPDATE a t SET t.name='tom'
where t.id in (
select id from (
select id,row_num() (partition by sex,order by id,name) rn from a where sex='男' and rn<=10) b)
where t.id in (
select id from (
select id,row_number() (partition by sex,order by id,name) rn from a where sex='男' and rn<=10) b)
修改没问题,不知道luoyoumou所说的是什么意思,等待他的模拟结果。
我也给你说个“记住”:不要企图在exists里用rownum控制条数,没有任何意义。exists代表存在,才不管你是1条还是10条。所以在这里minitoy是对的,你才是错的