我遇到此错误是在多表关联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条件解决。
