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

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.