I have tables account and account_devices
account table
id | account_id | state
1 | USD-001 | normal
2 | USD-002 | late
3 | USD-003 | default
4 | USD-004 | inactive
5 | USD-005 | inactive
account_devices table
id | account_id | start_date | end_date
1 | USD-001 | 2021-01-02 |
2 | USD-002 | 2021-02-03 |
3 | USD-003 | 2021-03-04 |
4 | USD-004 | 2021-04-05 |
5 | USD-005 | 2021-05-06 | 2022-01-01
Now I would like to set end_date of just inactive state accounts if that are not set (null)
I have tried with this update query:
update account_devices ad
set end_date = ad.start_date
from account_devices
join account on account.account_id = account_devices.account_id
where account_devices.end_date is null and account.state = 'inactive';
The problem with the above mention update query is to set end_date of all records whether its account state is inactive or not.
Here is the final result after the update query:
(It's not what I am expecting - problematic)
id | account_id | start_date | end_date
1 | USD-001 | 2021-01-02 | 2021-01-02
2 | USD-002 | 2021-02-03 | 2021-02-03
3 | USD-003 | 2021-03-04 | 2021-03-04
4 | USD-004 | 2021-04-05 | 2021-04-05
5 | USD-005 | 2021-05-06 | 2022-01-01
But actually I required something like that:
id | account_id | start_date | end_date
1 | USD-001 | 2021-01-02 |
2 | USD-002 | 2021-02-03 |
3 | USD-003 | 2021-03-04 |
4 | USD-004 | 2021-04-05 | 2021-04-05
5 | USD-005 | 2021-05-06 | 2022-01-01
Let me know what I am doing wrong.
Thanks for your attention. I'm looking forward to your reply.