UPDATE COALESCE query with null values

Definitely a basic question, but I couldn't find an example.

I'm writing a procedure which merges two rows into the good row. It moves all child rows' ids to being the correct one, replaces all NULL values with available values in the row being removed before finally deleting the 'bad' row.

What I have so far is this:

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

However, I'm getting non-descript error messages and I'm not sure why. I suspect there's an issue with how I'm handling my CTE and coalesce function, but I'm not sure where the gap in my understanding is.

Solution

In this statement :

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;

You are defining CTE bad_employee_values but you are not using it in the UPDATE part of the query, hence you cannot access its columns : for MySQL, bad_employee_values.employment_date is unknown.

It looks like you could simply avoid a CTE here. You could just self-join the table, like so :

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

This query will simply select the record identified by @core_emp_id, join it with the corresponding "bad" record, and copy the value of employment_date. The second condition in the WHERE clause prevents records whose employment_date is not null from being selected.