更新查询的问题
我有一个查询
UPDATE Table_1 SET Col1='Y' WHERE ROWID IN (
select ROWID from (
SELECT BUS_ID,
row_number() over (partition by BUS_ID order by BUS_ID) dupe_count,
rowid
from Table_1
WHERE col2 <> 1
AND col3 <> 1
order by dbms_random.value
) ft
where ft.dupe_count = 1
AND ROWNUM <= 1000
);
仅更新表 Table_1 中的 1000 行。
但是，如果我编写
UPDATE Table_1 SET Col1='Y' WHERE ROWID IN (
select ROWID from (
SELECT BUS_ID,
row_number() over (partition by BUS_ID order by BUS_ID) dupe_count,
rowid
from Table_1
WHERE col2 <> 1
AND col3 <> 1
order by dbms_random.value
) ft
where ft.dupe_count = 1
and Table_1.BUS_ID = ft.BUS_ID
AND ROWNUM <= 1000
);
它，它会更新表的所有行，而不管 RoWNUM <= 1000 即，如果我添加
Table_1.BUS_ID = ft.BUS_ID
，那么它会更新满足 col2<> 的所有行。 1 AND col3>> 1 和 ft.dupe_count=1。 该表具有以下结构：
BUS_ID | col1 | col2 | col3
1 | | 0 | 0
2 | | 0 | 0
1 | | 0 | 0
3 | | 1 | 1.
知道为什么会发生这种情况。请帮忙。
I have a query
UPDATE Table_1 SET Col1='Y' WHERE ROWID IN (
select ROWID from (
SELECT BUS_ID,
row_number() over (partition by BUS_ID order by BUS_ID) dupe_count,
rowid
from Table_1
WHERE col2 <> 1
AND col3 <> 1
order by dbms_random.value
) ft
where ft.dupe_count = 1
AND ROWNUM <= 1000
);
updates only 1000 rows in table Table_1.
But if i write
UPDATE Table_1 SET Col1='Y' WHERE ROWID IN (
select ROWID from (
SELECT BUS_ID,
row_number() over (partition by BUS_ID order by BUS_ID) dupe_count,
rowid
from Table_1
WHERE col2 <> 1
AND col3 <> 1
order by dbms_random.value
) ft
where ft.dupe_count = 1
and Table_1.BUS_ID = ft.BUS_ID
AND ROWNUM <= 1000
);
it updates all rows of the table irrespective of RoWNUM <= 1000 i.e if i add
Table_1.BUS_ID = ft.BUS_ID
then it updates all rows that satisfies col2<> 1 AND col3<> 1 and ft.dupe_count=1.
The table is having following structure:
BUS_ID | col1 | col2 | col3
1 | | 0 | 0
2 | | 0 | 0
1 | | 0 | 0
3 | | 1 | 1.
Any idea why is it happening.Please help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Niraj，
为每个表评估一个普通子查询。针对每一行评估相关子查询。并且您已在第二个更新语句中将子查询与
Table_1.BUS_ID = ft.BUS_ID行相关联。如果它对每一行进行计算，那么它将始终满足 ROWNUM <= 1000 谓词。问候，
抢。
Niraj,
An ordinary subquery is evaluated for each table. A correlated subquery is evaluated for each row. And you have made the subquery in your second update statement correlated with the line
Table_1.BUS_ID = ft.BUS_ID. And if it evaluates for each row, then it will always satisfy theROWNUM <= 1000predicate.Regards,
Rob.