Re: [GENERAL] Update with ORDER BY and LIMIT
Paul M Foster 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update with ORDER BY and LIMIT
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update with ORDER BY and LIMIT
> > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
