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

[Zach Seaman]

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`.

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]

On Wed, Feb 13, 2013 at 7:35 PM, Zach Seaman <znse...@gmail.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: gra...@gmail.com


--
Sent via pgsql-novice mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Zach Seaman]

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)

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.)