Search Postgresql Archives

Re: UPDATE of several columns using SELECT statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Mar 17, 2009 at 05:57:01PM +0100, adam.slachta wrote:
> 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) ?

That's quite a query; I'm guessing it's from Oracle as it refers to a
NVL function and that this should be translated into COALESCE for PG.
Would the following do what you want:

  UPDATE limit_breach lb SET
      limit_value = ov.hedge_limit,
      max_breach  = ov.max_breach
    FROM (
      SELECT hedging_desk, idmarket, symbol, limit_name, hedge_limit,
        ABS(ov.outright_volume) - COALESCE(ov.hedge_limit,0) AS max_breach
      FROM outrightvolume_breach) ov
    WHERE lb.hedging_desk = ov.hedging_desk
      AND lb.idmarket     = ov.idmarket
      AND lb.symbol       = ov.symbol
      AND lb.limit_name   = ov.limit_name
      AND lb.breach_end IS NULL
      AND lb.max_breach <= ov.max_breach;

I've obviously not tested it, but I think it's correct.  It's also got
the advantage of much less duplication of code.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux