Oracle - Updating table based on field from another table

Question

I have a Task table and a Project table. I'm trying to update the Percent Complete field in the Project table with the value of the Percent Complete field from the Tasks table. But I only want to update the field when the sequence code from the Task table is equal to 1. My failed attempt looks like this:

UPDATE inv_projects prj
SET prj.percent_complete = (SELECT NVL(tsk.prpctcomplete, 0)
                            FROM prtask tsk
                            WHERE tsk.prprojectid = prj.prid)
WHERE (SELECT tsk.prwbssequence
       FROM prtask tsk
       WHERE prj.prid = tsk.prprojectid) = 1;

I'm getting the error:

Error starting at line : 1 in command -
...
Error report -
ORA-01427: single-row subquery returns more than one row

I'm not quite sure what's going wrong.

Answer 1

Presumably, you want the sequence filtering in the subquery, like so

UPDATE inv_projects prj
SET prj.percent_complete = (
    SELECT NVL(tsk.prpctcomplete, 0)
    FROM prtask tsk
    WHERE tsk.prprojectid = prj.prid AND sk.prwbssequence = 1
)

This assumes that (prprojectid, prwbssequence) is unique in the task table, which seems consistent with your problem statement.

If there are projects without a task of sequence 1, and you don't want to update them, then use exists:

UPDATE inv_projects prj
SET prj.percent_complete = (
    SELECT NVL(tsk.prpctcomplete, 0)
    FROM prtask tsk
    WHERE tsk.prprojectid = prj.prid AND sk.prwbssequence = 1
)
WHERE EXISTS (
    SELECT 1
    FROM prtask tsk
    WHERE tsk.prprojectid = prj.prid AND sk.prwbssequence = 1
)

Or maybe your intent is to set the completion percent to 0 in this case; if so, move NVL() outside of the subquery:

UPDATE inv_projects prj
SET prj.percent_complete = NVL(
    (
        SELECT tsk.prpctcomplete
        FROM prtask tsk
        WHERE tsk.prprojectid = prj.prid AND sk.prwbssequence = 1
    ),
    0
)