From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | "J(dot)V(dot)" <jvsrvcs(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to update a newly added column with sub selects? |
Date: | 2012-04-28 20:23:34 |
Message-ID: | D5AEB796-36B4-4C8C-8055-32A2056BC0E7@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Apr 27, 2012, at 17:22, "J.V." <jvsrvcs(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | PostGres | 2012-04-28 23:43:23 | Deleting PostGres ID Under Windows XP? |
Previous Message | Jasen Betts | 2012-04-28 08:47:50 | Re: how to set up automatically startup database when the server boot or reboot. |