BEGIN
FOR I IN (SELECT D302_01 FROM TMP_IMPORT_MONEY WHERE D302_01 IS NOT NULL) LOOP
UPDATE D302
SET D302_20 =
(SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01)
WHERE D302_01 = I.D302_01;
END LOOP;
END;总是报错 ORA-01427: 单行子查询返回多个行
ORA-06512: 在 LINE 3谁知道这怎么处理啊
FOR I IN (SELECT D302_01 FROM TMP_IMPORT_MONEY WHERE D302_01 IS NOT NULL) LOOP
UPDATE D302
SET D302_20 =
(SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01)
WHERE D302_01 = I.D302_01;
END LOOP;
END;总是报错 ORA-01427: 单行子查询返回多个行
ORA-06512: 在 LINE 3谁知道这怎么处理啊
估计是SET D302_20 =
(SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01)
这个的问题
(
d302_01 NUMBER(20),
d302_20 NUMBER(14,4),
d302_22 VARCHAR2(20),
d301_21 VARCHAR2(20)
)
create table D302
(
d302_01 NUMBER(20) not null,
d302_05 NUMBER(20),
d302_02 DATE,
d302_03 NUMBER(14,4) default 0,
d302_04 NUMBER(14,4) default 0,
d302_21 CHAR(1) default 1,
d302_20 NUMBER(14,4) default 0,
d302_06 NUMBER(14,4) default 0,
d302_07 NUMBER(14,4) default 0,
d302_08 NUMBER(14,4) default 0,
d302_09 NUMBER(14,4) default 0,
d302_10 NUMBER(10) default 0,
d302_11 NUMBER(14,4) default 0,
d302_12 NUMBER(14,4) default 0,
d302_13 NUMBER(14,4) default 0,
d302_14 NUMBER(14,4) default 0,
d302_15 NUMBER(14,4) default 0,
d302_16 NUMBER(14,4) default 0,
d302_17 NUMBER(14,4) default 0,
d302_18 NUMBER(14,4) default 0,
d302_19 NUMBER(14,4) default 0,
d302_22 VARCHAR2(20),
d302_23 DATE default sysdate
)
(SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01)-- 等于('=')后面的子查询只能返回一行数据!
-- 即这个语句应该确保只返回一条数据:
SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01
FOR I IN (SELECT D302_01 FROM TMP_IMPORT_MONEY WHERE D302_01 IS NOT NULL) LOOP
UPDATE D302
SET D302_20 =
(SELECT D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01 AND ROWNUM<2)
WHERE D302_01 = I.D302_01;
END LOOP;
END;
返回多条记录,这样改试下
BEGIN
FOR I IN (SELECT D302_01 FROM TMP_IMPORT_MONEY WHERE D302_01 IS NOT NULL) LOOP
UPDATE D302
SET D302_20 =
(SELECT distinct D302_20 FROM TMP_IMPORT_MONEY WHERE D302_01 = I.D302_01)
WHERE D302_01 = I.D302_01;
END LOOP;
END;