Update in the same column from the same table

I’m trying to update a column in my table that was ignored at the initial insert based on a key and not null values in the same column.

My table is a history table in a data warehouse : it consists of (to simplify):

id which is its primary key
employee_id
date_of_birth
project_id

The rows help the company keep track of projects that an employee had worked on.

The problem is that when updating this table, the date_of_birth column is ignored, which is a problem for me since I’m working on a project that needs the age of the employee at the time he changed projects.

Actual:

+----+-------------+---------------+------------+
| ID | EMPLOYEE_ID | YEAR_OF_BIRTH | PROJECT_ID |
+----+-------------+---------------+------------+
|  1 |           1 | 1980          |          1 |
|  2 |           1 | NULL          |          2 |
|  3 |           2 | 1990          |          2 |
|  4 |           2 | NULL          |          1 |
+----+-------------+---------------+------------+

And this what I want:

+----+-------------+---------------+------------+
| ID | EMPLOYEE_ID | YEAR_OF_BIRTH | PROJECT_ID |
+----+-------------+---------------+------------+
|  1 |           1 |          1980 |          1 |
|  2 |           1 |          1980 |          2 |
|  3 |           2 |          1990 |          2 |
|  4 |           2 |          1990 |          1 |
+----+-------------+---------------+------------+

Answer

We could try using COALESCE to conditionally replace a NULL year of birth with a non NULL value:

SELECT
    ID,
    EMPLOYEE_ID,
    COALESCE(YEAR_OF_BIRTH, MAX(YEAR_OF_BIRTH) OVER (PARTITION BY EMPLOYEE_ID)) AS YEAR_OF_BIRTH,
    PROJECT_ID
FROM yourTable;

Advertisement

Answer