From: | Igor Romanchenko <igor(dot)a(dot)romanchenko(at)gmail(dot)com> |
---|---|
To: | Arvind Singh <arvindps(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update latest column in master table from transaction table |
Date: | 2012-11-03 14:01:38 |
Message-ID: | CAP95GqmGsJLacpsDuLgS4EL_q7PvXyEgRGU8C7hvJtPh8zFmGA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2012-11-03 16:46:43 | Re: Unexplained Major Vacuum Archive Activity During Vacuum |
Previous Message | Yvon Thoraval | 2012-11-03 09:23:52 | Re: PostgreSQL and IPV6 |