Update with ORDER BY and LIMIT - Mailing list pgsql-general

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