Query to "balance" rows across multiple tables

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

Responses

Browse pgsql-general by date

  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