Re: Insert output query to a column from a joined table in PostgreSQL 9.1

Thanks for the help, I got it.

UPDATE bmc.sisinst
SET solidokgd =
(CASE WHEN s.tipo = 1 THEN t.bsolidokgd

WHEN s.tipo = 2 THEN t.osolidokgd
ELSE t.osolidokgd
END),
biolld =
(CASE WHEN s.tipo = 1 THEN t.bbiolld

WHEN s.tipo = 2 THEN t.obiolld
ELSE t.obiolld
END),
biogasm3d =
(CASE WHEN s.tipo = 1 THEN t.bbiogasm3d

WHEN s.tipo = 2 THEN t.obiogasm3d
ELSE t.obiogasm3d
END)

On Thu, Feb 14, 2013 at 8:18 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Wed, Feb 13, 2013 at 7:35 PM, Zach Seaman <znseaman(at)gmail(dot)com> wrote:
> > I'm fairly new to PostgreSQL 9.1 and would like to set each of `CASE
> WHEN`
> > clauses to new columns in table `s` after joining to table `t`.
>
> Take a look at the UPDATE ... FROM ... construction
> http://www.postgresql.org/docs/9.2/static/sql-update.html.
>
> Here is the fast example from the documentation:
>
> UPDATE employees SET sales_count = sales_count + 1
> FROM accounts
> WHERE accounts.name = 'Acme Corporation'
> AND employees.id = accounts.sales_person;
>
> You can do JOINs in FROM here. Do not forget to alias updated table if
> it is used in FROM.
>
> >
> > This is my query:
> >
> > `SELECT s.tipo, s.mod,
> > CASE WHEN s.tipo = 1 THEN t.bsolidokgd
> > WHEN s.tipo = 2 THEN t.osolidokgd
> > ELSE t.osolidokgd
> > END AS solidokgd,
> > CASE WHEN s.tipo = 1 THEN t.bbiolld
> > WHEN s.tipo = 2 THEN t.obiolld
> > ELSE t.obiolld
> > END AS biolld,
> > CASE WHEN s.tipo = 1 THEN t.bbiogasm3d
> > WHEN s.tipo = 2 THEN t.obiogasm3d
> > ELSE t.obiogasm3d
> > END AS biogasm3d
> > FROM bmc.sisinst s INNER JOIN bmc.temperadoest t ON s.mod = t.mod;`
> >
> > Is there a way to `INSERT INTO` or `UPDATE` table `s` with a `CASE WHEN`
> > clause from table `t`?
> >
> > Thanks again for all the help,
> >
> > --
> > Zach Seaman
>
>
>
> --
> Sergey Konoplev
> Database and Software Architect
> http://www.linkedin.com/in/grayhemp
>
> Phones:
> USA +1 415 867 9984
> Russia, Moscow +7 901 903 0499
> Russia, Krasnodar +7 988 888 1979
>
> Skype: gray-hemp
> Jabber: gray(dot)ru(at)gmail(dot)com
>

--
*Zach Seaman****
GIS Expert, IRRI-México*
*Master of Regional & Community Planning
*
*m 55.2247.1740 (México)
m 01.913.4860.832 (U.S.)
*