Postgres Pro
Downloads
Home   >   mailing lists

Thread: Update with ORDER BY and LIMIT

Update with ORDER BY and LIMIT

From
Paul M Foster
Date:
Two tables:

1) cust (one record each customer)
    contains:
        a) lpmtdt (date = last payment date)
        b) lpmtamt (numeric = last payment amount)
        c) custno (varchar(6) = customer string)
2) cashh (one record each income/cash transaction)
    contains
        a) custno (varchar(6) = customer string)
        b) rcptamt (numeric = amount of receipt)
        c) rcptdt (date = date of receipt)

For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes
lacking values and shouldn't be. I want to update the customer table to
update these values from the cashh table. I don't want to use an
internal function. The PG version is 8.X.

I can get the proper updating record with:

SELECT rcptamt, rcptdt FROM cashh WHERE custno = 'COL1' ORDER BY rcptdt
DESC LIMIT 1;

(This gives me the latest cash receipt for this customer.)
But I can't seem to merge this with an "UPDATE cust ..." query so the
update happens in one step.

Any help?

Paul

--
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com

Re: Update with ORDER BY and LIMIT

From
"David Johnston"
Date:
For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking
values and shouldn't be. I want to update the customer table to update these
values from the cashh table. I don't want to use an internal function. The
PG version is 8.X.

--------------------------------------

No such version.  All PostgreSQL released versions use the numbers 0-9 and
periods only; no letters.

The general form for an UPDATE is:

UPDATE table
SET field = table2.field
FROM table2
WHERE table.field = table2.field;

SO:

UPDATE customer
SET lpmtdt = rcpt.rcptdt, lpmtamt = rcpt.rcptamt
FROM (SELECT custno, rcptdt, rcptamt FROM cashh WHERE ... ORDER BY ... LIMIT
1) rcpt
WHERE customer.custno = rcpt.custno AND customer.lptmdt IS NULL OR
customer.lpmtamt IS NULL

NOT TESTED


You WILL need to work on the sub-query if you hope to be able to do more
than 1 customer at a time.  In particular the use of WINDOW is very handy in
solving this particular but your non-existent version of PostgreSQL may not
have them available since they were introduced during the 8 series of
releases.  However, you can still write the sub-query to give you the
necessary lookup table but going a couple of levels deeper with sub-queries.

David J.

Re: Update with ORDER BY and LIMIT

From
Paul M Foster
Date:
On Mon, Aug 08, 2011 at 05:34:14PM -0400, David Johnston wrote:

> For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking
> values and shouldn't be. I want to update the customer table to update these
> values from the cashh table. I don't want to use an internal function. The
> PG version is 8.X.
>
> --------------------------------------
>
> No such version.  All PostgreSQL released versions use the numbers 0-9 and
> periods only; no letters.

8.X in this context means "8 point something, but I can't recall which
something". Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those
replying to restrict themselves to 8 series features, as opposed to 9
series features.

>
> The general form for an UPDATE is:
>
> UPDATE table
> SET field = table2.field
> FROM table2
> WHERE table.field = table2.field;
>
> SO:
>
> UPDATE customer
> SET lpmtdt = rcpt.rcptdt, lpmtamt = rcpt.rcptamt
> FROM (SELECT custno, rcptdt, rcptamt FROM cashh WHERE ... ORDER BY ... LIMIT
> 1) rcpt
> WHERE customer.custno = rcpt.custno AND customer.lptmdt IS NULL OR
> customer.lpmtamt IS NULL
>
> NOT TESTED
>

Works well enough as a starting point. Thanks.

Paul

--
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com

Re: Update with ORDER BY and LIMIT

From
David Johnston
Date:
>
> 8.X in this context means "8 point something, but I can't recall which
> something". Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those
> replying to restrict themselves to 8 series features, as opposed to 9
> series features.
>

There are a lot of features added between 8.0 and 8.4;  WITH and WINDOW being two major ones, that just saying 8 is not
helpful. In the future please take the time to issue a SELECT pg_version() before asking others to take time to help.
Itis for your own benefit and makes it easier for those wanting to help to give useful advice. 

David J.

Re: Update with ORDER BY and LIMIT

From
Paul M Foster
Date:
On Mon, Aug 08, 2011 at 10:20:18PM -0400, David Johnston wrote:

>
> >
> > 8.X in this context means "8 point something, but I can't recall
> > which something". Could be 8.2, 8.3 or 8.4. Thus, in effect, asking
> > those replying to restrict themselves to 8 series features, as
> > opposed to 9 series features.
> >
>
> There are a lot of features added between 8.0 and 8.4;  WITH and
> WINDOW being two major ones, that just saying 8 is not helpful.  In
> the future please take the time to issue a SELECT pg_version() before
> asking others to take time to help.  It is for your own benefit and
> makes it easier for those wanting to help to give useful advice.

It'd be great if select pg_version() worked, but PG doesn't recognize
the function, when issued from the PG prompt. I had to go all the way
back to aptitude to find out it's verson 8.3.1-1 running under Debian
unstable.

Paul

--
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com

Re: Update with ORDER BY and LIMIT

From
Tom Lane
Date:
Paul M Foster <paulf@quillandmouse.com> writes:
> It'd be great if select pg_version() worked, but PG doesn't recognize
> the function, when issued from the PG prompt.

It's "select version()".

            regards, tom lane