mysql - 使用空值更新 COALESCE 查询

Question

绝对是一个基本问题，但我找不到示例。

我正在编写一个程序，将两行合并到好行中。它将所有子行的 ID 移至正确的 ID，用要删除的行中的可用值替换所有 NULL 值，然后最终删除“坏”行。

到目前为止我所拥有的是:

CREATE DEFINER=`danielv`@`%` 
PROCEDURE `emp_merge`(IN `@core_emp_id` int, IN `@bad_emp_id` int)
BEGIN
    UPDATE claim SET employee_id = @core_emp_id 
    WHERE employee_id = @bad_emp_id;

    WITH bad_employee_values AS (
        SELECT * FROM employee WHERE employee_id = @bad_emp_id
    )
    UPDATE employee SET 
            employee.employment_date = COALESCE(employee.employment_date, bad_employee_values.employment_date),
    WHERE employee_id = @core_emp_id;

    DELETE FROM employee WHERE employee_id = @bad_emp_id;

END

但是，我收到了无法描述的错误消息，但我不确定原因。我怀疑我处理 CTE 和合并函数的方式存在问题，但我不确定我的理解差距在哪里。

Answer 1

在此声明中:

WITH bad_employee_values AS (SELECT * FROM employee WHERE employee_id = @bad_emp_id)
UPDATE employee SET 
        employee.employment_date = COALESCE(employee.employment_date, bad_employee_values.employment_date),
WHERE employee_id = @core_emp_id;

您正在定义 CTE bad_employee_values，但您没有在查询的 UPDATE 部分中使用它，因此您无法访问其列:对于 MySQL，bad_employee_values .employment_date 未知。

看来您可以简单地避免此处的 CTE。您可以自加入表，如下所示:

UPDATE employee e_core
INNER JOIN employee e_bad ON e_bad.employee_id = @bad_emp_id
SET e_core.employment_date = e_bad.employment_date,
WHERE employee_id = @core_emp_id AND e_core.employment_date IS NULL

此查询将简单地选择由 @core_emp_id 标识的记录，将其与相应的“坏”记录连接起来，然后复制 employment_date 的值。 WHERE 子句中的第二个条件阻止选择 employment_date 不为 null 的记录。