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,

[Sergey Konoplev]

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

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.

Post by Zach Seaman
`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 [phone]
Russia, Moscow [phone]
Russia, Krasnodar [phone]

Skype: gray-hemp
Jabber: ***@gmail.com

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

Post by Sergey Konoplev

Post by Zach Seaman
I'm fairly new to PostgreSQL 9.1 and would like to set each of `CASE

WHEN`

Post by Zach Seaman
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.
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.

Post by Zach Seaman
`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
USA [phone]
Russia, Moscow [phone]
Russia, Krasnodar [phone]
Skype: gray-hemp