解决ORA-01427错误：单行列子查询返回多行问题

问题背景 #

PART_LOCATION表中存在大量PART_NO、MFG、LOCATION匹配但BIN_LOC不同的记录，需要将同组内的备选仓位合并到该组TEMP_ID最小的主记录的ALT_BIN_XXX字段，随后删除组内其余非主记录。

原UPDATE语句在仅存在1个备选仓位时可正常执行，但存在多个备选仓位时触发ORA-01427: single-row subquery returns more than one row错误：

update PART_LOCATION a
    set alt_bin_loc_1 = 
    (
     select bin_loc
     from PART_LOCATION b where a.part_no = b.part_no and a.mfg = b.mfg and a.location = b.location and a.bin_loc <> b.bin_loc
     and a.temp_id < b.temp_id
    )
    where exists
    (
     select bin_loc
     from PART_LOCATION b where a.part_no = b.part_no and a.mfg = b.mfg and a.location = b.location and a.bin_loc <> b.bin_loc
     and a.temp_id < b.temp_id
    );

解决方案 #

步骤1：标记主记录与备选仓位排序 #

通过窗口函数给每组（按PART_NO、MFG、LOCATION分组）的记录排序，确定主记录（TEMP_ID最小，排序为1），并给备选仓位分配序号，用于后续映射到ALT_BIN_1、ALT_BIN_2等字段：

WITH ranked_bins AS (
    SELECT 
        part_no, mfg, location, bin_loc, temp_id,
        -- 标记主记录（TEMP_ID最小的为1）
        ROW_NUMBER() OVER (PARTITION BY part_no, mfg, location ORDER BY temp_id) AS record_rank,
        -- 给备选仓位按TEMP_ID排序，生成序号
        ROW_NUMBER() OVER (PARTITION BY part_no, mfg, location ORDER BY temp_id) - 1 AS alt_bin_seq
    FROM PART_LOCATION
)
SELECT * FROM ranked_bins;

步骤2：转换备选仓位为列并更新主记录 #

使用PIVOT将同组内的备选仓位转为多列，通过MERGE语句更新主记录的ALT_BIN_XXX字段：

MERGE INTO PART_LOCATION main
USING (
    SELECT 
        part_no, mfg, location,
        "1" AS alt_bin_loc_1,
        "2" AS alt_bin_loc_2,
        "3" AS alt_bin_loc_3 -- 根据实际备选数量扩展字段
    FROM (
        SELECT 
            part_no, mfg, location, bin_loc,
            ROW_NUMBER() OVER (PARTITION BY part_no, mfg, location ORDER BY temp_id) - 1 AS alt_seq
        FROM PART_LOCATION
        WHERE ROW_NUMBER() OVER (PARTITION BY part_no, mfg, location ORDER BY temp_id) > 1 -- 仅取备选记录
    )
    PIVOT (
        MAX(bin_loc)
        FOR alt_seq IN (1, 2, 3) -- 对应alt_bin_loc_1/2/3，按需添加更多序号
    )
) alt_bins
ON (main.part_no = alt_bins.part_no AND main.mfg = alt_bins.mfg AND main.location = alt_bins.location)
-- 仅更新组内TEMP_ID最小的主记录
WHERE main.temp_id = (SELECT MIN(temp_id) FROM PART_LOCATION WHERE part_no = main.part_no AND mfg = main.mfg AND location = main.location)
WHEN MATCHED THEN UPDATE SET
    main.alt_bin_loc_1 = alt_bins.alt_bin_loc_1,
    main.alt_bin_loc_2 = alt_bins.alt_bin_loc_2,
    main.alt_bin_loc_3 = alt_bins.alt_bin_loc_3; -- 按需扩展更新字段

步骤3：删除非主记录 #

完成主记录更新后，删除组内TEMP_ID不是最小的冗余记录：

DELETE FROM PART_LOCATION
WHERE (part_no, mfg, location, temp_id) NOT IN (
    SELECT part_no, mfg, location, MIN(temp_id)
    FROM PART_LOCATION
    GROUP BY part_no, mfg, location
);

补充说明 #

• 若备选仓位数量超过3个，只需在PIVOT的IN子句和MERGE的更新字段中添加对应序号（如4对应alt_bin_loc_4）即可。
• 所有操作建议先在测试环境验证，避免误操作生产数据。

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