Update latest column in master table from transaction table
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
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@hotmail.com> wrote:
Show quoted text
hello,
i have two tables
customer_mastercname
lastreceiptdate
lastreceiptamtaccounts
cname
date
amounti 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
tableso 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