今天的一段批量更新的sql语句,稍微修改了一下就报错:ORA-01427: 单行子查询返回多个行。这个问题其实之前有遇到过几次,原因也比较简单,就是没有做好子查询的约束条件,导致该返回一条数据的地方返回了多条。下面这段sql其实不应该报错的,只是我在多个数据库环境中切换的时候,没有同时更新到最新的表结构导致的。跳过这段SQL,详细分析一下这个问题出现的原因以及有效的避免方法。
UPDATE PO_ITEMS_VENDORS PIV
SET (ITEM_ID,
VENDOR_ID,
ORG_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ENABLE_FLAG,
ATTRIBUTE3,
LAST_UPDATED_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ATTRIBUTE4) =
(SELECT MI.ITEM_ID,
HO.ORG_ID,
PASL.USING_ORGANIZATION_ID,
PASL.OWNING_ORGANIZATION_ID,
PASL.VENDOR_BUSINESS_TYPE,
PASL.ASL_STATUS_ID,
PASL.VENDOR_SITE_ID,
PASL.LAST_UPDATE_DATE,
PASL.LAST_UPDATED_BY,
PASL.CREATION_DATE,
PASL.CREATED_BY,
PASL.VENDOR_CODE
FROM MTL_ITEMS MI,
HR_ORGANIZATION HO,
PO_APPROVED_SUPPLIER_LIST_TEMP PASL
WHERE 1 = 1
AND HO.ORG_TYPE = 'SUPPLY'
AND MI.ITEM_CODE = PASL.ITEM_CODE
AND HO.ORG_CODE = PASL.VENDOR_CODE)
WHERE EXISTS (SELECT 1
FROM PO_APPROVED_SUPPLIER_LIST_TEMP PASL
WHERE PIV.HEADER_ID = PASL.ASL_ID)
网上很多人说在子查询中加上一个约束条件
AND ROWNUM=1
这个条件确实能阻止sql报错,但是,风险比较大。在没有写好表关联条件的情况下,贸然加上这么一句,更新的内容,或者查询出来的内容,并非一定是你想要的。其实更加正确的做法是通过唯一约束条件来关联多个表。比如上面的sql中,ITEM_CODE,ORG_CODE分别是MTL_ITEMS ,HR_ORGANIZATION的唯一约束条件,这样匹配查询出来的数据,肯定不会多,但可能少。什么时候少呢?那就是PO_APPROVED_SUPPLIER_LIST_TEMP 中的ITEM_CODE或者VENDOR_CODE没有对应匹配值的时候。
另外,在保证子查询约束条件写好的情况下,加上一句 AND ROWNUM=1 可以在一定程度上提高子查询的效率,毕竟,查到一条符合条件的数据后,后面即使有再多数据,都不会再去扫描了。