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

Question

ORA-01427: single-row subquery returns more than one row in update statement

Hi. For below query, ORA-01427 error occurred.

I'm trying to get birthday from country_population and set it as fave_date in city_population. Other simpler way to execute this is not needed, I just need some suggestion how to eliminate the error and let the query work. Thank you :)

    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;

The error is relatively clear, the sub-query is returning more than 1 row for the criteria supplied - either you need to refine those criteria more, or in the event of multiple rows make an aggregate decision about what to choose — Andrew

ScaisEdge ScaisEdge · Accepted Answer · 2018-08-24T08:38:50

the query

(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)

return more than a row so you can't use with a equal assigment ..

in this case you could add more selective where condition for get only a row result

or if there si not one result only anyway use an aggregation function for reduce the result eg: min() or 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
                      )

or use limit for rows result

(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)

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

1 Answers