Обсуждение: UPDATE from a SELECT on two fields.

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.

On Fri, Jul 08, 2005 at 05:31:58PM -0600, Roy Souther wrote:
> I have been useing an UPDATE from a SELECT on a single field and it
> works great.

<snip>

> 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?

Doesn't that work? Off the top of my head I can't see any reason why
that wouldn't work. This works for me:

update testtable set field1 = x, field2 = y
from (select 1 as x, 2 as y) as foo;

Hope this helps,

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

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.