I have a table like this:
| ID | start_date | end_date |
|---|---|---|
| 1 | 09/01/2022 | |
| 1 | 09/04/2022 | |
| 2 | 09/01/2022 |
I have another reference table like this:
| ID | date | owner |
|---|---|---|
| 1 | 09/01/2022 | null |
| 1 | 09/02/2022 | null |
| 1 | 09/03/2022 | Joe |
| 1 | 09/04/2022 | null |
| 1 | 09/05/2022 | Jack |
| 2 | 09/01/2022 | null |
| 2 | 09/02/2022 | John |
| 2 | 09/03/2022 | John |
| 2 | 09/04/2022 | John |
For every ID and start_date in the first table, I need find rows in the reference table that occur after start_date, and have non-null owner. Then I need to update this date value in end_date of first table.
Below is the output that I want:
| ID | date | end_date |
|---|---|---|
| 1 | 09/01/2022 | 09/03/2022 |
| 1 | 09/04/2022 | 09/05/2022 |
| 2 | 09/01/2022 | 09/02/2022 |