Issue
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?
Solution
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)
Answered By - Cornel Fîntînă Answer Checked By - Terry (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.