CODEWITHSUNDEEP
pythonsqlpython-3.xpostgresql
martin
martin

Reputation: 885

Inserting columns with Postgresql by ID

I'm working with this movie database and I need to insert the column "director_id" in the movie table. This is what I'm dealing with:

enter image description here

Above is the directors table.

Below is the movie table. As you can see, I have a director_id and a movie ID.

enter image description here

This is where I need to insert the director_id to. But I need to match each director to its corresponding movie (using the id). It should be very simple, but since I'm new to databases, I can't do it. I searched everywhere but I couldn't solve it. Thanks in advance for your help!

Upvotes: 0

Views: 52

Answers (1)

The Impaler
The Impaler

Reputation: 48810

I think you are looking for a JOIN.

For example:

select
  m.id,
  m.title,
  d.director_id
from movie m
join directors d on d.movie_id = m.id

EDIT: Adding redundancy column "director_id"

If you want to add the column director_id permanently you'll need to add the column first (empty), and then update its value. For example:

alter table movie add column director_id int;

And then:

update movie m
set director_id = d.director_id
from directors d
where d.movie_id = m.id

Mind that the UPDATE statement is run at this moment, and won't keep updating the data in the future, unless you run it again. Therefore, the column director_id may become stale, having information from the time when the UPDATE was run.

Upvotes: 1

Related Questions