Update with ORDER BY and LIMIT
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
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.
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 NULLNOT TESTED
Works well enough as a starting point. Thanks.
Paul
--
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com
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.
David J.
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
