multiply two fields and update different table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiply two fields and update different table

Author

Topic

Waxman
Starting Member

6 Posts

Posted - 2010-07-28 : 17:52:22

Hi,

Hope someone can help.

I have two tables;

1 MenuItem
Fields include, PLU, COST, DESCRIPTION

2 KIT
Fields include, PARENTPLU, QTY, COST

I need to Link (Join?) the tables on MenuItem.PLU = Kit.PARENTPLU then multiply the Kit.Qty * Kit.Cost and put the sum into MenuItem.cost

Need help, thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 17:58:15

Try this:
UPDATE mi
SET COST = k.QTY*k.COST
FROM MenuItem mi
JOIN KIT k
ON mi.PLU = k.PARENTPLU

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Waxman
Starting Member

6 Posts

Posted - 2010-07-28 : 18:00:48

I'll try that, but I want to learn. What is Mi? Some sort of memory variable? How about K?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 18:10:32

They are just aliases. You don't need them, but I prefer less typing. Some here hate aliases, I'm fond of them. I try to use 1-2 letters for my aliases to keep it short.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Waxman
Starting Member

6 Posts

Posted - 2010-07-28 : 18:12:17

Thanks...starting to make some cents

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 18:16:51

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-28 : 20:51:50

quote:
Originally posted by tkizer

Some here hate aliases, I'm fond of them. I try to use 1-2 letters for my aliases to keep it short.

I'm with you on that!

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 13:09:54

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

It will also help if you will learn the HUGE differences between rows and records, fields and column, and tables and files.

Let's try to write some DDL to repqalce your vague narratives

CREATE TABLE MenuItems
(plu INTEGER NOT NULL PRIMARY KEY,
item_cost DECIMAL (8,2) NOT NULL,
item_description VARCHAR(100) NOT NULL);

CREATE TABLE Kits
(parent_plu INTEGER NOT NULL,
kit_qty INTEGER NOT NULL,
kit_cost DECIMAL (8,2) NOT NULL);

What is a "plu"? I guess it is an industry standard identifier
Why is a parent_plu a totally different kind of entity from a mere plu? The prefix should be used when a plu plays a role. You need to learn how to do a data model and ISO-11179 rules.

Did you notice that Kits cannot ever have a key, and therefore by definition is never a real table?

>> I need to link (join?) the tables on MenuItems.plu = Kits.parent_plu then multiply the Kits.kit_qty * Kits.kitcost and put the sum into MenuItems.item_cost <<

A link is an old term used in network databases for a one-way chain of pointers . A join is a relational operator. Again, they are nothing alike.

Your whole approach is wrong. A virtual table is a table and does not have to be materialized like a file. Your Menu Items is a summary of Kits, so put it in a VIEW that is always correct. SQL is not like punch cards where you need to have a physical file.

Now I have to make guess about the data. Kits are parts in a Bill of Materials for a Menu item. The menu item needs to be defined as a set of kits

-- parts
CREATE TABLE Kits
(plu INTEGER NOT NULL PRIMARY KEY,
kit_qty INTEGER NOT NULL,
kit_cost DECIMAL (8,2) NOT NULL);

--assemblies
CREATE TABLE Menu_Items
(menu_item_id INTEGER NOT NULL,
plu INTEGER NOT NULL
REFERENCES Kits(plu),
item_description VARCHAR(100) NOT NULL,
PRIMARY KEY (menu_item_id, plu));

-- summary view of costs
CREATE VIEW MenuCosts (menu_item_id, item_description, menu_item_cost_tot)
AS
SELECT menu_item_id, item_description,
SUM(kit_qty * kit_cost) AS menu_item_cost_tot
FROM Kits AS K, Menu_Items AS M
WHERE K.plu = M.plu;

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Waxman
Starting Member

6 Posts

Posted - 2010-07-29 : 18:04:02

Thanks for your input, but if you read the posts, Tara had already answered the q.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources