Postgres Pro
Downloads
Home   >   mailing lists

Re: trying to write a bit of logic as one query, can't seem to do it under 2 - Mailing list pgsql-general

From Tom Lane
Subject Re: trying to write a bit of logic as one query, can't seem to do it under 2
Date
Msg-id 19590.1271896409@sss.pgh.pa.us
Whole thread Raw
In response to trying to write a bit of logic as one query, can't seem to do it under 2  (Jonathan Vanasco <postgres@2xlp.com>)
Responses Re: trying to write a bit of logic as one query, can't seem to do it under 2  (Glen Parker <glenebob@nwlink.com>)
List pgsql-general
Tree view 
Jonathan Vanasco <postgres@2xlp.com> writes:
> I'm trying to write a bit of logic as 1 query, but I can't seem to do
> it under 2 queries.

Uh, why can't you just push that CASE expression into the sub-select?

UPDATE
    cart_item
SET
    qty_requested_available =
 ( SELECT CASE
        WHEN
            qty_available > qty_requested THEN qty_requested
        ELSE
            qty_available
    END
   FROM stock where stock.id = stock_id )
;

You might have to qualify qty_requested here to make sure it comes from
cart_item, if there's a column of the same name in stock.

BTW, I'd suggest using GREATEST() instead of the CASE, but that's
just a minor improvement.

            regards, tom lane

pgsql-general by date:

Previous
From: Jonathan Vanasco
Date:
Subject: trying to write a bit of logic as one query, can't seem to do it under 2
Next
From: Giles Lean
Date:
Subject: Re: Database viewpoint of subject - Sending e-mails from database table with Cronjob