On Apr 27, 2012, at 17:22, "J.V." <jvsrvcs@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
