Re: Update with ORDER BY and LIMIT

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