Thread: 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?
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?
|
Attachment
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.