Thread: Copying a column from one table to another

Suppose I have a table "people" like this;

Name   Age
David    25
Simon   19

and a table "occupations" like this:

Name  Occupation
David  Teacher
Simon  Student

"Name", "Age", and "Occupation" are just the column names, not data.
The names are unique and one-to-one, i.e. if there's a David in one
table then there's exactly one David in each.

I know that if I want to display

David  25  Teacher
Simon 19  Student

I can use something like

select people.name, age, occupation from people, occupations where
people.name=occupations.name

But, what I would like to know is, how can I copy the column
"occupation" to the table "people"?  That is, I want to create a new
column people.occupation and populated it from occupations.occupation.
Feel free to point me to a relevant section of the documentation.

David

On Wed, 15 Mar 2006 17:12:59 -0500
David Gaudine <davidg@alcor.concordia.ca> threw this fish to the penguins:

> Suppose I have a table "people" like this;
>
> Name   Age
> David    25
> Simon   19
>
> and a table "occupations" like this:
>
> Name  Occupation
> David  Teacher
> Simon  Student
>
> "Name", "Age", and "Occupation" are just the column names, not data.
> The names are unique and one-to-one, i.e. if there's a David in one
> table then there's exactly one David in each.
...
> But, what I would like to know is, how can I copy the column
> "occupation" to the table "people"?  That is, I want to create a new
> column people.occupation and populated it from occupations.occupation.
> Feel free to point me to a relevant section of the documentation.

As you just said, you must first create a new column:
   alter table people add column occupation text;

then you update the people table with appropriate values:

   update people set people.occupation=o.occupation from occupations o where people.name=o.name;

Read about "alter table" and "update" SQL commands in the postgresql manual:
  http://www.postgresql.org/docs/8.1/interactive/dml-update.html
  http://www.postgresql.org/docs/8.1/interactive/ddl-alter.html

-- George Young

--
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)