如何在第二个子查询中访问要更新的表?让我来解释一下,我有一个更新查询:
UPDATE someTbl tbl
SET
x_price = (
SELECT
substr(
func.x_price,
7,
instr(
func.x_price,
'=',
7
) - 7
)
FROM
(
SELECT
somePackage.someFunction(
pbi.product_id,
pbi.x_sset_id,
pbi.inst_prod_id,
SYSDATE,
'',
'',
'',
''
) AS x_price
FROM
someTbl pbi
) func
);并且我必须创建限制第二个子查询的条件,因为我得到了
ORA-01427: single-row subquery returns more than one row
我需要建立条件来定义主表tbl和第二子查询func之间的关系,我是oracle sql的初学者。你能帮我吗?
发布于 2017-06-30 16:17:37
消息"ORA-01427:单行子查询返回多行“返回当结果信任为单行但结果为多行时,上述set field语句值必须为一行一值!
对不起,英语不好
发布于 2017-06-30 16:24:18
您需要将这两个查询关联起来:
UPDATE someTbl tbl
SET x_price = (
SELECT substr( x_price, 7, instr( x_price, '=', 7 ) - 7 )
FROM (
SELECT somePackage.someFunction(
product_id,
x_sset_id,
inst_prod_id,
SYSDATE,
'',
'',
'',
''
) AS x_price,
your_primary_key -- Add primary key to inner query
FROM someTbl
) func
WHERE tbl.your_primary_key = func.your_primary_key
-- Add filter to outer query which will
-- correlate the rows from the update and
-- the sub-query.
);否则,子查询将返回表中的所有行。
https://stackoverflow.com/questions/44841687
复制相似问题