Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE of several columns using SELECT statement

UPDATE of several columns using SELECT statement

From
"adam.slachta"
Date:

Hello,

 

I am facing the following problem, nothing tough, I guess (hope)….think it should be some syntactical problem:

 

1. When trying to update two columns

UPDATE myFirstTable SET (fistCol, secCol) = (  SELECT anotherFistCol, anotherSecondCol FROM mySecondTable )

 

I am getting: ERROR:  syntax error at or near "select"

 

 

2. When changed to (only the parentheses are changed):

UPDATE myFirstTable SET (fistCol, secCol) = ((  SELECT anotherFistCol, anotherSecondCol FROM mySecondTable ))

 

I am getting: ERROR:  number of columns does not match number of values

 

Does anybody know, what I am doing wrong? Thank you very much and wish you a luxurious day

 

Adam

Re: UPDATE of several columns using SELECT statement

From
Sam Mason
Date:
On Tue, Mar 17, 2009 at 03:55:47PM +0100, adam.slachta wrote:
> 1. When trying to update two columns
>
> UPDATE myFirstTable SET (fistCol, secCol) = (  SELECT anotherFistCol, anotherSecondCol FROM mySecondTable )

The more common say of expressing this would be something like:

  UPDATE myFirstTable a SET
      fistCol = b.anotherFistCol,
      secCol  = b.anotherSecondCol
    FROM mySecondTable b
    WHERE a.expr = b.expr;

> 2. When changed to (only the parentheses are changed):
>
> UPDATE myFirstTable SET (fistCol, secCol) = ((SELECT anotherFistCol, anotherSecondCol FROM mySecondTable ))
>
> I am getting: ERROR:  number of columns does not match number of values

PG is somewhat ad-hoc with its support of its record syntax and
unfortunately doesn't support the above at the moment.  The error is
coming from very early on (I think maybe when parsing) and I don't think
it ever gets as far as checking that the value coming back from the
select is a record of the same structure as is on the left hand side.

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

Re: UPDATE of several columns using SELECT statement

From
Richard Huxton
Date:
adam.slachta wrote:
>
> 1. When trying to update two columns
>
> UPDATE myFirstTable SET (fistCol, secCol) = (  SELECT anotherFistCol,
> anotherSecondCol FROM mySecondTable )

In addition to Sam's reply what you've got there would never work anyway
unless mySecondTable only contains one row. You'd need a join. Like Sam
says though, we don't support that syntax.

--
  Richard Huxton
  Archonet Ltd

Re: UPDATE of several columns using SELECT statement

From
"adam.slachta"
Date:

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:

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

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

 
 

Re: UPDATE of several columns using SELECT statement

From
Sam Mason
Date:
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/