Hello,
try something like
WITH lastreceipt as
(SELECT DISTINCT ON (acc.cname) acc.cname, acc.date, acc.amount
FROM accounts acc
ORDER BY acc.date DESC)
UPDATE customer_master
SET lastreceiptdate = lr.date
lastreceiptamt = lr.amount
FROM lastreceipt lr
WHERE cname = lr.cname
(Haven't tested it. You may need to correct some mistakes before it works)
The idea is:
1) form the list of last receipts in the WITH part
2) use previously formed list in FROM part of UPDATE
On Sat, Nov 3, 2012 at 9:03 AM, Arvind Singh <arvin...@hotmail.com> wrote:
> hello,
>
> i have two tables
> customer_master
> > cname
> > lastreceiptdate
> > lastreceiptamt
> accounts
> > cname
> > date
> > amount
>
> i need help in constructing a single update query. where the
> customer_master table is updated with the latest receipt date and receipt
> amount for a single customer code (cname like "FRUITXXXXX") from accounts
> table
>
> so far we are using a select command to retrieve a record with max(Date)
> and then using another update command to update using results from the
> select query.
>
> thanks
>
>
>
