I've been asked to write an update query and I'm not sure where to start. In our database a client record can have many addresses in the address table and the basic task is to update the client's address records so that only one is "active". For each client, if there is only one address, don't touch the address. If there are multiple addresses, don't touch the address record with the newest "start date", and set the "end date" for each of the older address records. The trick is that the "end date" should be equal to the "start date" of the next address record for that client. If an "end date" is already present, then it should remain untouched.
Below are examples of the expected results. Any help would be much appreciated.
update table x
set end_date = (select min(t.start_date)
from table t
where t.id = x.id
and t.start_date > x.start_date)
where x.end_date is null
and 1 < (select count(*)
from table c
where x.id = c.id)