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 <arvindps@xxxxxxxxxxx> 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
