如何用SELECT和WHERE子句在同表更新指定列？解决SQL子查询报错

原语句的问题分析 #

你的原SQL报错有两个核心原因：

1. WHERE条件里用=匹配子查询结果，但子查询通过GROUP BY返回了所有员工的emp_emp_cnic，属于多值结果，=只能匹配单值，直接触发报错。
2. 即便把=改成IN，也会把每个员工的所有任职记录都更新，完全不符合“只更新最新任职记录”的需求。

可行解决方案 #

方案1：用窗口函数精准定位最新记录（适用于支持窗口函数的数据库：MySQL 8+/PostgreSQL/SQL Server等）

通过ROW_NUMBER()窗口函数给每个员工的任职记录按入职日期倒序排名，排名为1的就是最新记录，再匹配更新：

WITH ranked_postings AS (
    SELECT 
        employee_posting_id,
        ROW_NUMBER() OVER (
            PARTITION BY emp_emp_cnic 
            ORDER BY employee_posting_from DESC, employee_posting_id DESC
        ) AS rn
    FROM employeepostinghistory
)
UPDATE employeepostinghistory e
SET employee_posting_to = NULL
WHERE EXISTS (
    SELECT 1 
    FROM ranked_postings r
    WHERE r.employee_posting_id = e.employee_posting_id
    AND r.rn = 1
);

注：额外加employee_posting_id DESC是为了避免同一员工同一天有多条任职记录时，只更新主键最大的那条（保证唯一性），如果不需要可以去掉。

方案2：用分组关联更新（适用于不支持CTE的旧版数据库）

先分组找出每个员工的最新入职日期，再通过关联匹配到对应的记录进行更新：

UPDATE employeepostinghistory e
INNER JOIN (
    SELECT 
        emp_emp_cnic,
        MAX(employee_posting_from) AS latest_post_date
    FROM employeepostinghistory
    GROUP BY emp_emp_cnic
) latest ON e.emp_emp_cnic = latest.emp_emp_cnic 
AND e.employee_posting_from = latest.latest_post_date
SET e.employee_posting_to = NULL;

注：如果同一员工同一天有多条任职记录，这个方案会把这些记录的employee_posting_to都设为NULL，需要根据业务需求调整。

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