update时 单行子查询返回多个行 SQL 错误 [1427] 处理方案

我遇到此错误是在多表关联update的

UPDATE  EDASYS.CELL_COMPONENT_T A  
SET A.ARRAY_GLASS_ID = (SELECT M.ARRAY_GLASS_ID FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M WHERE M.CF_GLASS_ID = A.COMPONENT_ID AND  rownum < 2) 
WHERE  EXISTS (SELECT 1 FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M WHERE  1=1 AND A.STEP_ID = 'L1110' AND A.COMPONENT_START_TIME >= TO_DATE('2018-06-03','yyyy-mm-dd')
AND A.COMPONENT_START_TIME <= TO_DATE('2018-06-27','yyyy-mm-dd')
AND A.COMPONENT_ID LIKE 'C%')

在关联M表进行更新A表的一个字段的时候，总是会报单行子查询返回多个行。一开始百思不得其解。

-----------------------------------------------------2018/09/09 update-------------------------------------------

今天在update CELL2_CHIP_T的cf_chip_id & chip_no时又遇到同样的问题，难道是上次没有理解这个问题？我又仔细看了上次的笔记，结论就是   对上次的解决方案理解不透彻。

既然能意识到子查询关联的字段有多笔记录导致的。但是无法解决，是因为我这次做的update是在在同一张表上做操作。思维定式上认为两张表关联update时才会用此种解决方案。其实为啥 把同一张表 起两个别名 难道不一样？请看如下sql

UPDATE EDASYS.CELL2_CHIP_T A SET A.ITEM29 = 
(SELECT (B.CF_GLASS_ID || '02') FROM EDASYS.CELL2_CHIP_T B WHERE 
A.CHIP_ID = B.CHIP_ID 
AND B.CHIP_START_TIME >= TO_DATE('2018-09-09 00:00','yyyy-mm-dd HH24:MI:SS')
AND B.CHIP_START_TIME <= TO_DATE('2018-09-09 00:10','yyyy-mm-dd HH24:MI:SS')
AND  substr(B.PRODUCT_ID,3,3) = '495' AND substr(B.CHIP_ID,11,2) = '01' AND B.CF_GLASS_ID IS NOT NULL  
AND  rownum < 2)
WHERE 
EXISTS(SELECT 1 FROM EDASYS.CELL2_CHIP_T B where
1=1 AND A.CHIP_START_TIME >= TO_DATE('2018-09-09 00:00','yyyy-mm-dd HH24:MI:SS')
AND A.CHIP_START_TIME <= TO_DATE('2018-09-09 00:10','yyyy-mm-dd HH24:MI:SS')
AND  substr(A.PRODUCT_ID,3,3) = '495' AND substr(A.CHIP_ID,11,2) = '01' AND A.CF_GLASS_ID IS NOT NULL )

 

请注意斜体的部分，是同一张表。用这种方法update完全可以完成任务，在同一张表中，根据一个字段update另外一个字段。

1、在子查询中增加 AND  rownum < 2 --拿到子查询中的一笔记录？接下来才是关键 如何和两外一张表把这笔唯一记录关联起来

2、将外层查询修改为 where exits(SELECT 1 FROM table_B where A.表的条件 ) --加租的这句话就是从B表中拿一条满足A表查询条件的数据去update.

为什么要增加exists？

如果不加exists  ，没有匹配上的字段都会置为空值；所以必须要加 where exists   限制条件。 
比如 cell_component_t 中存在一条 array_glass_id ='A18A3211CG' 的记录，而 cell_array-cf_mapping_t没有， 
不加  where exists   限制， cell_component_t 中array_glass_id ='A18A3211CG'记录的array_glass_id 字段将被置为空值，原来的值会被清除。 

 

 

虽然此种方案可以完成任务，但是极其繁琐，需要每一模修改一次SQL。

于是请教了一下老大，他用了一个及其巧妙的SQL：

--495
update cell2_chip_t set item30= trim(to_char( trunc((chip_no+1)/2) * 4-1 - chip_no,'00')) ,item29= trim(cf_glass_id) || trim(to_char( trunc((chip_no+1)/2) * 4-1 - chip_no,'00')) where 1=1 and record_index >= 201712010000000000 and  record_index < 201712020000000000 and product_id like'%495%' and (substr(item29,1,1) <>'C' or item29  is null);commit;
--315
update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201808010000000000 and  record_index < 201808020000000000 and product_id like'%315%' and (substr(item29,1,1) <>'C' or item29  is null);commit;
--575
update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201802010000000000 and  record_index < 201802020000000000 and (product_id like'%575%' or product_id like'%315%') and (substr(item29,1,1) <>'C' or item29  is null);commit;
--695
--not panel 5
update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201807010000000000 and  record_index < 201807020000000000 and (product_id like'%695%') and chip_no<'05' and (substr(item29,1,1) <>'C' or item29  is null);commit;
-- is Panel 5
update cell2_chip_t set item30= '05',item29=  trim(cf_glass_id) || '05' where 1=1 and record_index >= 201807010000000000 and  record_index < 201809080000000000 and (product_id like'%695%') and AND chip_no = '05' and (substr(item29,1,1) <>'C' or item29  is null);commit;

真的是事半功倍。

此外还要注意如果在子查询中增加查询条件会提高效率。

SELECT * FROM (
SELECT COUNT(1),ARRAY_GLASS_ID  FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M GROUP BY ARRAY_GLASS_ID ORDER BY COUNT(1) DESC)

看到这个结果我知道了，因为EDASYS.CELL_ARRAY_CF_MAPPING_T有502条为-1的记录。就是子查询关联的字段有多笔记录导致的。

看前辈我是这样解决的：

UPDATE  EDASYS.CELL_COMPONENT_T A  
SET A.ARRAY_GLASS_ID = (SELECT M.ARRAY_GLASS_ID FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M WHERE M.CF_GLASS_ID = A.COMPONENT_ID AND  rownum < 2) 
WHERE  EXISTS (SELECT 1 FROM EDASYS.CELL_ARRAY_CF_MAPPING_T M WHERE  1=1 AND A.STEP_ID = 'L1110' AND A.COMPONENT_START_TIME >= TO_DATE('2018-06-03','yyyy-mm-dd')
AND A.COMPONENT_START_TIME <= TO_DATE('2018-06-27','yyyy-mm-dd')
AND A.COMPONENT_ID LIKE 'C%')

 

 

另外一个例子

更新同一张表中的两个字段

 

UPDATE
CELL_DEFECT_T b
SET
B.NUM_ITEM26 =- B.NUM_ITEM1,
B.NUM_ITEM27 = B.NUM_ITEM2
WHERE
b.RECORD_INDEX IN(
SELECT
B.RECORD_INDEX
FROM
cell_component_t A
LEFT JOIN CELL_DEFECT_T B ON
A.RECORD_INDEX = B.RECORD_INDEX
WHERE
A.COMPONENT_ID LIKE 'C%'
AND B.NUM_ITEM1 <> '-1'
AND B.NUM_ITEM2 <> '-1'
AND B.NUM_ITEM26 = '0'
AND A.UPDATE_TIME >= TO_DATE( '2018-05-25 10:53:24', 'YYYY-MM-DD HH24:MI:SS' )
AND A.UPDATE_TIME < TO_DATE( '2018-05-26 10:53:24', 'YYYY-MM-DD HH24:MI:SS' )
)

---------------------------------------------------------------

这里主要涉及的是 DML锁表的问题。

Oracle数据库的锁类型 

在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

在数据行上只有X锁（排他锁）。在Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。 

1.根据A表的某列去update B表的某列时，一定要找出A B两张表可以关联的所有字段，这样基本上不会出现"ORA-01427: 单行子查询返回多个行";
2.如果A表中真的有重复行，那就加上rownum<2条件解决。