Update with ORDER BY and LIMIT
От | Paul M Foster |
---|---|
Тема | Update with ORDER BY and LIMIT |
Дата | |
Msg-id | 20110808202415.GE21240@quillandmouse.com обсуждение исходный текст |
Ответы |
Re: Update with ORDER BY and LIMIT
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: