gpt4 book ai didi

sql - ORA-01427 : single-row subquery returns more than one row in Update Statement

转载 作者:行者123 更新时间:2023-12-01 13:18:44 34 4
gpt4 key购买 nike

ORA-01427: 单行子查询在更新语句中返回多于一行

你好。对于以下查询,发生 ORA-01427 错误。

我正在尝试从 country_population 获取生日并将其设置为 city_population 中的 fave_date。不需要其他更简单的方法来执行它,我只需要一些建议如何消除错误并让查询工作。
谢谢 :)

    DECLARE
CURSOR custCur IS
SELECT name, age, weight, height, birthday FROM country_population;
CNTR number(9) := 0;
BEGIN
FOR curRec IN custCur LOOP

UPDATE city_population srvagr
SET srvagr.date1 = SYSDATE,
srvagr.fave_date =
(select curRec.birthday from country_population curRec
where srvagr.name=curRec.name
and srvagr.age=curRec.age
and srvagr.weight=curRec.weight
and srvagr.height=curRec.height),
srvagr.date2 = SYSDATE,
srvagr.date3 = SYSDATE,
srvagr.status = 'visitor',
srvagr.note = 'Noted'
WHERE
srvagr.name = curRec.name
and srvagr.age = curRec.age
and srvagr.weight = curRec.weight
and srvagr.height = curRec.height
and srvagr.status != 'visitor'
and srvagr.fave_date is null;

CNTR := CNTR + 1;
if CNTR = 1000
then
COMMIT;
CNTR := 0;
end if;

END LOOP;

COMMIT;

最佳答案

查询

(select curRec.birthday from country_population curRec
where srvagr.name=curRec.name
and srvagr.age=curRec.age
and srvagr.weight=curRec.weight
and srvagr.height=curRec.height)

返回多于一行,所以你不能使用相等的分配..

在这种情况下,您可以添加更多选择性 where 条件以获取仅行结果

或者如果没有一个结果,无论如何使用聚合函数来减少结果,例如:min() 或 max()
(select min(curRec.birthday)  from country_population curRec
where srvagr.name=curRec.name
and srvagr.age=curRec.age
and srvagr.weight=curRec.weight
and srvagr.height=curRec.height
)

或对行结果使用限制
(select min(curRec.birthday)  from country_population curRec
where srvagr.name=curRec.name
and srvagr.age=curRec.age
and srvagr.weight=curRec.weight
and srvagr.height=curRec.height
and rownum = 1)

关于sql - ORA-01427 : single-row subquery returns more than one row in Update Statement,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52000370/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com