Oracle UPDATE遇ORA-01427错误，求职位字段英化解决方案

问题背景 #

HR_ALL_POSITIONS_F表的name字段存储格式如MER.Fiyatlandırma Müdür Yardımcısı.，通过以下SQL可提取出中间的土耳其语职位标识（格式为.Fiyatlandırma Müdür Yardımcısı.）：

SELECT name, SUBSTR(
         name,
         INSTR(name, '.', 1, 1),
         INSTR(name, '.', 1, 2) + 1 - INSTR(name, '.', 1, 1)
       ) AS deneme
FROM   HR_ALL_POSITIONS_F;

现有pozisyontanimlama表存储土耳其语职位（TURKISHPOSITION字段）与英语职位（ENGLISHPOSITION字段）的对应关系，需要将denememusa123表的eklenecekkolon字段更新为匹配的英文职位名称。

尝试执行以下UPDATE语句时触发错误：

UPDATE denememusa123
   SET denememusa123.eklenecekkolon = (SELECT ENGLISHPOSITION FROM pozisyontanimlama)
 WHERE (SELECT SUBSTR (name,
                       INSTR (name,
                              '.',
                              1,
                              1),
                         INSTR (name,
                                '.',
                                1,
                                2)
                       + 1
                       - INSTR (name,
                                '.',
                                1,
                                1))
          FROM HR_ALL_POSITIONS_F) = (Select TURKISHPOSITION FROM pozisyontanimlama);

报错信息：ORA-01427: single-row subquery returns more than one row

错误原因 #

原语句中的两个子查询均未设置关联条件：

• (SELECT ENGLISHPOSITION FROM pozisyontanimlama)会返回表中所有英文职位，是多行结果
• (SELECT SUBSTR(...) FROM HR_ALL_POSITIONS_F)会返回所有提取出的土耳其语职位标识，也是多行结果

用=运算符比较两组多行结果，Oracle无法确定每行的匹配关系，因此触发单行子查询返回多行的错误。

解决方案 #

需要通过关联条件将三个表的行一一对应，确保denememusa123的每一行都能找到唯一匹配的英文职位。以下提供两种可行方案：

方案1：带关联条件的UPDATE语句（Oracle 12.1+支持） #

假设denememusa123与HR_ALL_POSITIONS_F通过position_id字段关联，请根据实际情况替换关联字段：

UPDATE denememusa123 d
SET eklenecekkolon = (
    SELECT p.ENGLISHPOSITION
    FROM pozisyontanimlama p
    JOIN HR_ALL_POSITIONS_F h 
        ON SUBSTR(h.name, INSTR(h.name, '.', 1, 1), INSTR(h.name, '.', 1, 2) + 1 - INSTR(h.name, '.', 1, 1)) = p.TURKISHPOSITION
    WHERE h.position_id = d.position_id
)
WHERE EXISTS (
    -- 仅更新存在匹配关系的行
    SELECT 1
    FROM pozisyontanimlama p
    JOIN HR_ALL_POSITIONS_F h 
        ON SUBSTR(h.name, INSTR(h.name, '.', 1, 1), INSTR(h.name, '.', 1, 2) + 1 - INSTR(h.name, '.', 1, 1)) = p.TURKISHPOSITION
    WHERE h.position_id = d.position_id
);

方案2：MERGE语句（兼容所有Oracle版本） #

MERGE语句更适合这种跨表关联更新的场景，逻辑更清晰：

MERGE INTO denememusa123 d
USING (
    -- 预先关联HR表和职位对照表，得到需要更新的映射关系
    SELECT 
        h.position_id, -- 替换为实际关联字段
        p.ENGLISHPOSITION
    FROM HR_ALL_POSITIONS_F h
    JOIN pozisyontanimlama p 
        ON SUBSTR(h.name, INSTR(h.name, '.', 1, 1), INSTR(h.name, '.', 1, 2) + 1 - INSTR(h.name, '.', 1, 1)) = p.TURKISHPOSITION
) src
ON (d.position_id = src.position_id) -- 匹配条件
WHEN MATCHED THEN
    UPDATE SET d.eklenecekkolon = src.ENGLISHPOSITION;

注意事项 #

• 必须确认denememusa123与HR_ALL_POSITIONS_F之间的关联字段（如示例中的position_id），否则无法建立行与行的对应关系。
• 若pozisyontanimlama中存在同一土耳其语职位对应多个英语职位的情况，需要先清理数据，确保映射关系唯一，否则仍会触发单行子查询返回多行的错误。

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