Обсуждение: 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 NULL > > NOT 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. Itis 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
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