SQL1只找到一百多的记录,SQL2却更新了两万多记录,麻烦看看哪里问题---SQL 1
select data0006.WORK_ORDER_NUMBER,
data0006.PROD_RTE_PTR,
data0050.PROD_ROUTE_PTR
from data0006,data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR
and data0006.PROD_STATUS in (2,3,206,306);
--SQL 2
update data0006 set prod_rte_ptr =
(select prod_route_ptr
from data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR)
where data0006.PROD_STATUS in (2,3,206,306);
select data0006.WORK_ORDER_NUMBER,
data0006.PROD_RTE_PTR,
data0050.PROD_ROUTE_PTR
from data0006,data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR
and data0006.PROD_STATUS in (2,3,206,306);
--SQL 2
update data0006 set prod_rte_ptr =
(select prod_route_ptr
from data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR)
where data0006.PROD_STATUS in (2,3,206,306);
--SQL 1
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR
and data0006.PROD_STATUS in (2,3,206,306);
--SQL 2
where data0006.PROD_STATUS in (2,3,206,306);
update data0006 set prod_rte_ptr =
(select prod_route_ptr
from data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR)
where data0006.PROD_STATUS in (2,3,206,306);
中,实际上update的where语句仅仅是“where data0006.PROD_STATUS in (2,3,206,306);”,select语句中的where语句并不能限定update的更新范围。
这个要求就是把data0050的Route Key, 更新到data0006,如何改写? 谢谢!
update data0006 set prod_rte_ptr =
(select prod_route_ptr
from data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR)
where PROD_STATUS in (2,3,206,306) and CUST_PART_PTR in (select rkey from data0050) and PROD_RTE_PTR not in (select PROD_ROUTE_PTR from data0050)没有环境,没有经过测试
and exists
(select * from data0050
where data0006.CUST_PART_PTR = data0050.rkey
and data0006.PROD_RTE_PTR <> data0050.PROD_ROUTE_PTR);
ORACLE技术交流,oracle开发、sql优化、管理。
欢迎各位oracle爱好者加入本群,本群为oracle爱好者进阶、技术讨论群,主旨为oracle技术交流、讨论。
欢迎所有oracle爱好者进群,谢绝非oracle求助、插科打诨。
群号: 144243235
非诚勿扰。
update 语句,只要不写 where 条件,就是更新所有行,如果子查询中没有找到记录,则被更新的列,将会被更新成 null ;