I am trying to update, if an employee has a termination_date, then update the next join_date to termination_date - 1 day in postgresql. The first Join_date should not be updated.
Example:
| EmpID | Dept_No | Join_Date | termination_date | ID |
|---|---|---|---|---|
| 1001 | 11 | 2020-01-01 | 2020-02-01 | 183 |
| 1001 | 12 | 2020-02-02 | 2020-04-01 | 182 |
| 1001 | 11 | 2020-04-02 | 186 |
Below is the output that I am trying to get.
| Dept_No | Join_Date | termination_date |
|---|---|---|
| 11 | 2020-01-01 | 2020-02-01 |
| 12 | 2020-02-01 | 2020-04-01 |
| 11 | 2020-04-01 |
Below is what I have so far,
UPDATE employee e
SET Join_Date =
((termination_date) -INTERVAL '1 DAY')
WHERE termination_date IS NOT NULL;
But I am not sure how to add the below conditions,
Could anyone advice how to add that? Thanks in advance.
Use EXISTS in the UPDATE statement:
UPDATE employee AS e1
SET Join_Date = e1.Join_Date -INTERVAL '1 DAY'
WHERE EXISTS (
SELECT 1
FROM employee e2
WHERE e2.EmpID = e1.EmpID AND e2.termination_date IS NOT NULL
AND e2.Join_Date < e1.Join_Date
)
See the demo.
Results:
| empid | dept_no | join_date | termination_date | id |
|---|---|---|---|---|
| 1001 | 11 | 2020-01-01 | 2020-02-01 | 183 |
| 1001 | 12 | 2020-02-01 | 2020-04-01 | 182 |
| 1001 | 11 | 2020-04-01 | null | 186 |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments