1

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.

1
  • update with what? and what if then where a 5 Ferraris and 8 Renaults?
    – nbk
    Sep 15, 2020 at 22:06

1 Answer 1

0

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 together

In 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

CREATE TABLE Cars  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp 
);

INSERT INTO Cars 
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('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');
✓

4 rows affected
  CREATE TABLE Tmp_table  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp 
);

INSERT INTO Tmp_table 
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('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', '2020-10-13 10:12:58', '2020-10-13 15:12:58');
✓
4 rows affected
 UPDATE Cars
SET "Last_seen_in_garage" = maxtempcar."maxdateime"
FROM (SELECT DISTINCT Cars."Type",
       MAX("Last_seen_in_garage") OVER (PARTITION BY Cars."Type") "maxdateime"
       FROM Cars) maxcar 
INNER JOIN (SELECT DISTINCT Tmp_table."Type",
MAX("Last_seen_in_garage") OVER (PARTITION BY Tmp_table."Type") "maxdateime"
FROM Tmp_table) maxtempcar ON maxtempcar."Type" = maxcar."Type" 
WHERE Cars."Type" = maxcar."Type" AND Cars."Last_seen_in_garage" = maxcar."maxdateime";
3 rows affected
SELECT *
FROM Cars
;
Type    | First_seen_in_garage | Last_seen_in_garage
:------ | :------------------- | :------------------
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  | 2020-10-13 15:12:58

db<>fiddle here

11
  • The update will occur from the data of the next day so in this case the interval trick will not work for me because I have to get the data from the temporary table in which I have stored the new Last_seen_in_garage values
    – d4bbi
    Sep 16, 2020 at 9:31
  • as i commented and wrote also here, the query is sound,and takes the little information ´, that you gave. I think you can go from there and see if it works, else post the query, for clarification
    – nbk
    Sep 16, 2020 at 9:36
  • instead of update Cars you take any table you want, what th eupdatein table needs is Type and the connectiomng date to identofy the row that must be updated.
    – nbk
    Sep 16, 2020 at 11:12
  • The table Cars allows me to have duplicates in the column type but whenever I get the new data I have to update only the last seen value of the last occurrence of each distinct type
    – d4bbi
    Sep 16, 2020 at 11:27
  • that ios what the updates makes, as you see with the Ferrari, only the row with '2002-10-13 15:12:58 as Last_seen_in_garage gets update to 2002-10-13T15:17:58.000Z, so it does what you wanted.the subselct gets the last of each type and uses that to update the correct row
    – nbk
    Sep 16, 2020 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.