Обсуждение: 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
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:
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