How to insert data in a date column from a different table with the oldest date using postgreSQL-CodePudding

I have 2 tables trainings and training_instances. Please see the schema below:

trainings table:

column_name   data_type     is_nullable. foreign_key

id            uuid          NO           NULL
day           int4          NO           NULL
start_time    time(0)       NO           NULL
end_time      time(0)       NO           NULL
team_id       uuid          YES          public.teams(id)
inserted_at   timestamp(0)  NO           NULL
updated_at    timestamp(0)  NO           NULL

Note that trainings table does not have a date or start_date column yet. The column named day is the day of the week. One training_id can have multiple training instances.

training_instances table:

column_name   data_type     is_nullable. foreign_key
id            uuid          NO           NULL
date          date          NO           NULL
start_time    time(0)       NO           NULL
end_time      time(0)       NO           NULL
team_size     int4          YES          NULL
training_id   uuid          YES          public.trainings(id)
team_id       uuid          YES          public.teams(id)
inserted_at   timestamp(0)  NO           NULL
updated_at    timestamp(0)  NO           NULL

I am trying to create a new column named start_date in the trainings table and then insert the data here for existing trainings. I want to insert for every training the oldest date for its corresponding training instance in this new start_date column.

What would be the best way to do this?

CodePudding user response：

Firstly, create the column using

alter table public.trainings add "start_date" date null;

Then, try to execute this query

update trainings 
  set start_date = (select min(ti.date) from training_instances ti where 
    trainings.team_id = ti.team_id and 
    trainings.id = ti.training_id)

CodePudding user response：

After creating the column you can try this way.

UPDATE trainings 
SET    start_date = training_instances.min_date
FROM   trainings as t
JOIN (select id, training_id, MIN(date) AS min_date from training_instances GROUP BY id, training_id) AS training_instances ON t.id = training_instances.training_id