On Thu, Apr 20, 2017 at 8:38 AM, Tom Dawson <[email protected]> wrote:
> Hi All
>
> I have a stock table which records the locations and quantities of various
> items. The layout of the table is like so:
Tom:
The problem with keeping a total in each row of the tables is that
each record is now dependant on other records in the table. Each time
you add or subtract a quantity from one row, you'll need to
recalculate the rest. Also, if you add or delete a row, again, you'll
need to do the re-total. This is "de-normalization" and leads to many
problems and harder-to-maintain code.
That said, if you want to do it,
Here's setting up your data example:
CREATE TABLE example (stockid C(5), location C(5) , quantity N(5), total n(5))
INSERT INTO example VALUES ("1","A",10,0)
INSERT INTO example VALUES ("1","B",20,0)
INSERT INTO example VALUES ("2","C",50,0)
INSERT INTO example VALUES ("2","D",50,0)
UPDATE example SET total=(select SUM(quantity) from example ex2 WHERE
example.stockid=ex2.stockid)
But don't do that :)
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/CACW6n4vObxinB3Xn_5Tn9VN+gBJNpQKEy+pFRVVpP_whP=i...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
