From: | "adam(dot)slachta" <adam(dot)slachta(at)xitee(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org>, <sam(at)samason(dot)me(dot)uk>, <dev(at)archonet(dot)com> |
Subject: | Re: UPDATE of several columns using SELECT statement |
Date: | 2009-03-17 16:57:01 |
Message-ID: | CDE77F346A704F04ABF883EA3853E67C@milano |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Well, I should have been more specific and not use such a simplified
example, which only lead you into wrong direction. What I am really tried
to solve instead of
UPDATE myFirstTable SET (fistCol, secCol) = ( SELECT anotherFistCol,
anotherSecondCol FROM mySecondTable )
is this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
UPDATE limit_breach lb
SET (max_breach, limit_value) =
((
SELECT ABS(ov.outright_volume) - NVL(ov.hedge_limit,0),
hedge_limit
FROM
(
outrightvolume_breach
)
ov
WHERE ov.hedging_desk=lb.hedging_desk
AND ov.idmarket = lb.idmarket
AND ov.symbol = lb.symbol
AND ov.limit_name = lb.limit_name
AND lb.breach_end IS NULL
))
WHERE
lb.breach_end IS NULL
AND
(lb.hedging_desk, lb.idmarket, lb.symbol, lb.limit_name)
IN
(
SELECT hedging_desk, idmarket, symbol, limit_name
FROM
(
outrightvolume_breach
) ov
WHERE (ABS(ov.outright_volume) - NVL(ov.hedge_limit,0)) >
lb.max_breach
) ;
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
Do you think there might be a way how to solve this not leading into
splitting the update into two separate update statements for each of the two
columns (max_breach, limit_value) ?
Adam
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Manesajian | 2009-03-17 16:59:08 | Re: using window functions |
Previous Message | Joshua D. Drake | 2009-03-17 16:50:40 | Re: PostgreSQL versus MySQL for GPS Data |