UPDATE from a SELECT on two fields. - Mailing list pgsql-general

From Roy Souther
Subject UPDATE from a SELECT on two fields.
Date
Msg-id 1120865518.16796.9.camel@UtopiaPlanetia
Whole thread Raw
Responses Re: UPDATE from a SELECT on two fields.
List pgsql-general
I have been useing an UPDATE from a SELECT on a single field and it works great.

Example:
UPDATE reports.mytemptable SET igka =
(SELECT
CASE
  WHEN value >= 0 THEN value || ' (G}'
  WHEN value < 0 THEN (value * -1) || ' (K)'
END
FROM (
SELECT ((mmpuncorvol - uutuncorvol)::float4 / 1000)::NUMERIC(5,3) AS value
FROM runs
WHERE status ILIKE '%init%'
AND status ILIKE '%fast%'
AND inactive='f'
AND evaluation = reports.mytemptable.eid ) AS idontcare)

To save some time I would like it to update two fields at the same time but I cannot wrap my head around how that could be done. Can it not be done?

I would like to make it do something like this...
Example:
UPDATE reports.mytemptable SET igkrv = mmpuncorvol, igka = givekeepamount FROM (
SELECT mmpuncorvol,
CASE
  WHEN value >= 0 THEN value || ' (G}'
  WHEN value < 0 THEN (value * -1) || ' (K)'
END AS givekeepamount
FROM (
SELECT mmpuncorvol, ((mmpuncorvol - uutuncorvol)::float4 / 1000)::NUMERIC(5,3) AS value
FROM runs
WHERE status ILIKE '%init%'
AND status ILIKE '%fast%'
AND inactive='f'
AND evaluation = reports.mytemptable.eid ) AS idontcare) AS stilldontcare

Any ideas?


Roy Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.



Attachment

pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: Postgresql 7.4.8 inconsistent index usage
Next
From: "Adam Pritchard"
Date:
Subject: index usage in multi-column ORDER BY