下面的SQL语句正在抛出错误:
ORA-01427:单行子查询返回多个行
UPDATE TABLE_1 T1
SET ( COL1, COL2, COL3, COL4) = (
SELECT col1
, col2
, col3
, col4
FROM TEMP_TABLE_2 tt2
WHERE tt2.COL_XYZ = t1.COL_XYZ)
WHERE EXISTS ( select null
FROM TEMP_TABLE_2 tt2
WHERE tt2.COL_XYZ = t.COL_XYZ);
我相信这个问题在"存在的地方"上
有什么想法?
使用内联视图(如果被Oracle视为可更新)
注意:如果面对非密钥保留的行错误,请添加索引以使其更新 - 可更新
UPDATE (SELECT t1.col1 AS o_col1,
t1.col2 AS o_col2,
t1.col3 AS o_col3,
t1.col4 AS o_col4,
tt2.col1 AS n_col1,
tt2.col2 AS n_col2,
tt2.col3 AS n_col3,
tt2.col4 AS n_col4
FROM table_1 T1
inner join temp_table_2 tt2
ON tt2.col_xyz = t1.col_xyz) T
SET o_col1 = o_col1,
o_col2 = n_col2,
o_col3 = n_col3,
o_col4 = n_col4;