Re: a join between tables and a 'selective' update

A basic principle of the relational model is that all relationships are shown by values in columns in the rows of tables. If two rows hold the same values, then you cannot tell them apart. What you might want is a quantity column:

CREATE TABLE Inventory
(upc CHAR(13) NOT NULL,
expire_date DATE NOT NULL,
PRIMARY KEY (upc, expire_date),
qty_on_hand INTEGER NOT NULL
CHECK ( quantity_on_hand >= 0),
...);

Now you do an update like this, assumng that we are now using the new 13-digit UPC/EAN code for inventory at the store.

UPDATE Inventory
SET qty_on_hand
= qty_on_hand -10
WHERE upc = '1234567890123 -- code for milk AND expire_date
= (SELECT MIN(expire_date)
FROM Inventory
WHERE upc = '1234567890123');

It is trickier if you have to go over two or more expiration dates to get the desired quantity. You have to find the earliest expiration date that has (n) or more units before it and work with that. It helps to draw a stacked bar chart of the units in each date to see this.

CREATE VIEW RunningInventory (upc, expire_date, run_total_qty) AS
SELECT I1.upc, i1.expire_date, SUM(I2.qty_on_hand) FROM Inventory AS I1, Inventory AS I2
WHERE I1.expire_date >= I2.expire_date
AND I1.upc = I2.upc;

I have to go to bed, but does this help? Google on LIFO and FIFO in SQL for some detailed code. Received on Thu Jan 20 2005 - 04:17:40 CET