Re: Update latest column in master table from transaction table

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 <arvindps(at)hotmail(dot)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
>
>
>