Re: Update with ORDER BY and LIMIT

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.