PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Monday, October 24, 2022

[FIXED] How to insert data in a date column from a different table with the oldest date using postgreSQL

 October 24, 2022     join, postgresql, sql-update     No comments   

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)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing