Re: How to update a newly added column with sub selects?

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
thiscolumn.  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
singlestatement. 
>>
>> 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
giveto few details to provide more specific help. 
>
> David J.
>
>