[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] How to update a newly added column with sub selects?
From:       "J.V." <jvsrvcs () gmail ! com>
Date:       2012-04-30 15:34:47
Message-ID: 4F9EB117.9030006 () gmail ! com
[Download RAW message or body]

Here are additional details (in addition to the original post) on what I 
am trying to do with a single update statement:

Suppose three tables:
     1) person
     2) person_home
     3) group

     1) table:  person                   3) table: group
     column:    person_id              column: group_id
     *column: zip_code                 column: zip_code      ( for each 
row in person, I need to get the zip_code here and put into 
person.zip_code)

     2) table:    person_home
     column: person_home_id
     column: person_id
     column: group_id




This is not the real example, but demonstrates what I am trying to do.

I want to add a new column "zip_code" to the "person" table, and 
ultimately make it a foreign key to group(zip_code).

So I must:
     1. Add the column "zip_code" to "person" with no constraints
     2. populate the column (by ultimately looking it up in the "group" 
table with a subselect)
     3. add a not null and fk constraint to person(zip_code) referencing 
group(zip_code)


---
Part number 2 is what I am having problem with.

For each person.person_id (for each row), I need to do a sub select to 
get to group_id (in the real example, I have to go through many other 
tables, but this
is the simplest example)

To get the zip_code for person.person_id=1, I would do:
     select zip_code from group where group_id= (select group_id from 
PersonHome where person_id=1)
     save that in a variable and do an update on that person row with 
the zip_code returned.

---

The person_id is hard coded (or could be substituted by a variable:

To do an update to populate one row, after the new "zip_code" column has 
been added, I would do:

     update person set zip_code = (select zip_code from group where 
group_id = (select group_id from person_home where person_id=1))

---
now I *do not* want to:
     select person_id from person; <= select all id's from the table at 
once, and put the entire result set into an array
     iterate through each element of the array and put the update inside 
a loop substituting each person_id from the array at each iteration,
     and updating each row one by one.

Ideally would simply like one single update statement to do it all, but 
not sure if there is a facility for this, or
how it could be done.

thanks for your  help.

J.V.

On 4/28/2012 2:23 PM, David Johnston wrote:
> On Apr 27, 2012, at 17:22, "J.V."<jvsrvcs@gmail.com>  wrote:
> 
> > I need to add a new column to a table (nullable), then populate and then add a \
> > not null constraint. 
> > The value of the new column is obtained by doing three or more nested sub-selects \
> > to get the id that should go into this column.  At this point I can add a not \
> > null and foreign key constraint. 
> > Ideally would like to do this with a single updated statement, but not sure how:
> > 
> > So for example, given a table, I have to select the id from that table, and for \
> > each id, pull id's from the next table, and from there use that id for the next \
> > and so on. 
> > select id from table; is the id I am starting with, so this might show
> > 
> > 1
> > 2
> > 3
> > 4
> > 
> > update table set new_column_id = (select id2 from join_table2 where \
> > new_column_id=2); 
> > but I do not want to write a loop and iterate through this stament passing \
> > 1,2,3,4 to the above statement, just a single statement. 
> > Is this possible?
> > 
> > thanks
> > 
> > 
> > J.v.
> > 
> Try an update of this form:
> 
> UPDATE table SET col = s.newvalue
> FROM ( SELECT id, newvalue FROM ... ) s
> WHERE s.id = table.id;
> 
> I would expect simple joins to work but if not you can always try WITH RECURSIVE \
> instead of a procedural loop.  You give to few details to provide more specific \
> help. 
> David J.
> 
> 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]