From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Query to "balance" rows across multiple tables |
Date: | 2010-07-02 09:24:24 |
Message-ID: | AANLkTimen8lMdqC6aagrBnfKEMnrO0p8pK8Sal-6t8BA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have two tables. One for the quantity of an item I need to buy, and
one for the quantity of an item I already own. I want to "balance"
these two tables so that I subtract the amount I need from what I
own..
For example:
ToBuy
---------
Item A: 6
Item B: 5
Item C: 3
Owned
---------
Item A: 1
Item B: 5
Item C: 5
The results should be:
ToBuy
----------
Item A: 5 (since I already own one)
Item B: 0 (since I already had five)
Item C: 0 (Since I already had five)
Owned
---------
Item A: 0 (since I used up the one I had)
Item B: 0 (since I used up all five)
Item C: 2 (since I used 3, but have 2 left)
Here's the catch: I want to DELETE any row (in either table) that has
zero quantity, since I no longer need this data (plus I have a CHECK
constraint on this value and require it to be positive anyway)..
Is there actually a way to do this in a SQL function? Thanks!
Mike
From | Date | Subject | |
---|---|---|---|
Next Message | Babu R | 2010-07-02 10:46:37 | bulk insert using COPY and PHP code |
Previous Message | Wappler, Robert | 2010-07-02 07:10:31 | Re: Uncable to commit: transaction marked for rollback |