Oracle SQL动态批量更新多行：根据另一表状态更新字段

Let's break down why your current SQL is throwing that error, then fix it with proper syntax that handles your requirement (updating all rows for an employee to match their status in table1).

Why You're Getting the Error #

The single-row subquery returns more than one row error happens because your nested subqueries are returning multiple values for a single row in the target table.

For example, in your second SQL statement, you're joining PS_JOB_CURR_VW with the table you're updating (PS_RH_EE_LITIGATN). Since an employee can be in multiple groups (so multiple rows in the target table), this join returns multiple rows per employee—giving the database multiple HR_STATUS values to pick from, which it can't do.

Solution 1: Use MERGE (Recommended for Oracle/PeopleSoft) #

MERGE is perfect here because it explicitly matches rows between the source table (table1/your view) and target table (table2/PS_RH_EE_LITIGATN), and updates all matching rows in one go.

For your first table scenario:

MERGE INTO table2 t2
USING table1 t1
ON (t1.emplid = t2.emplid)
WHEN MATCHED THEN
    UPDATE SET t2.has_documents = CASE WHEN t1.status = 'A' THEN 'Y' ELSE 'N' END;

For your PeopleSoft-specific scenario:

MERGE INTO SYSADM.PS_RH_EE_LITIGATN P
USING PS_JOB_CURR_VW J
ON (J.EMPLID = P.EMPLID)
WHEN MATCHED THEN
    UPDATE SET P.RH_RR_DOC = CASE WHEN J.HR_STATUS = 'A' THEN 'Y' ELSE 'N' END;

Solution 2: Use a Correlated Subquery with EXISTS #

If you prefer sticking with UPDATE, you can rewrite the subquery to only fetch the status once per employee, and add an EXISTS clause to ensure we only update rows that have a matching employee in the source table.

For your first table scenario:

UPDATE table2 t2
SET has_documents = (
    SELECT CASE WHEN t1.status = 'A' THEN 'Y' ELSE 'N' END
    FROM table1 t1
    WHERE t1.emplid = t2.emplid
)
WHERE EXISTS (
    SELECT 1 FROM table1 t1 WHERE t1.emplid = t2.emplid
);

For your PeopleSoft scenario:

UPDATE SYSADM.PS_RH_EE_LITIGATN P
SET RH_RR_DOC = (
    SELECT CASE WHEN J.HR_STATUS = 'A' THEN 'Y' ELSE 'N' END
    FROM PS_JOB_CURR_VW J
    WHERE J.EMPLID = P.EMPLID
)
WHERE EXISTS (
    SELECT 1 FROM PS_JOB_CURR_VW J WHERE J.EMPLID = P.EMPLID
);

How This Works #

Both solutions ensure that for each row in the target table, we fetch exactly one status value from the source table (since emplid should be unique in table1/PS_JOB_CURR_VW). All rows in the target table for the same employee will get the same has_documents/RH_RR_DOC value, matching your expected result.

Testing with your sample data:

• Employee 100002 (status A) gets Y in both their rows
• Employee 100001 (status I) gets N
• Employee 100003 (status A) gets Y
• Employee 100004 (status I) gets N

内容的提问来源于stack exchange，提问作者Cedrick Champagne