I have a database of a garage of rented cars with a table called Cars with the following columns :
Type,First_seen_in_garage,Last_seen_in_garage
The table initially have the following rows :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-13 11:12:58
Renault,2002-10-13 09:12:58,2002-10-13 12:12:58
Ferrari,2002-10-13 10:12:58,2002-10-13 15:12:58
Now I have the following row from which I have to update the initial table :
*Ferrari,2002-10-14 08:12:58,2002-10-14 10:12:58*
*Renault,2002-10-13 23:12:58,2002-10-14 23:12:58*
*Lambo,2002-10-13 23:12:58,2002-10-14 23:12:58*
in order to become :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-14 23:12:58
Renault,2002-10-13 09:12:58,2002-10-14 23:12:58
Ferrari,2002-10-13 10:12:58,2002-10-14 10:12:58
Note that for the Ferrari rows I want to keep the first instance as it is and update only the second one
I could do it with an UPDATE .. SET .. WHERE if there was only one Ferrari instance however the previous query updates the two rows which is not what I want ..
Help Please.
Best Answer
As you don't tell us ´,w hat for an Update you will have,
I selected one . where the lst of a type get 5 Minutes more on his End time, this is of corse only an example for an update, to show how it works.
The latest seen time we get from the WINDOW Function
MAX OVER PArtition
And the update only update the types with the latest time (i choose Last_seen_in_garage) because the update only updates the rows , that are joined togetherIn the new query i also check out the same data from the temptaböe(see that Ferrair has now 2020 date as Last_seen_in_garage and use those data to update the CARS table
db<>fiddle here