Combining Sales - SQL Server Forums

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

Combining Sales

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-17 : 05:10:47

How to UPDATE Quantity into the SALES table if OrderID not found else INSERT?

Scenario:
1. OrderID is Primary Key
2. OrderNum is repeatable

[Sales]

SalesID | ItemNum | Quantity
1 | 10 | 100
2 | 20 | 200
3 | 30 | 300
4 | 40 | 400

1. If the User change the ItemNum of SalesID #2 from 20 to a new ItemNum. If the new ItemNum exist, it should combine the Quantity:
Update Quantity=Quantity+200 WHERE SalesID = 1 to become:
1 | 10 | 300
Then DELETE SalesID #2

or another way is INSERT INTO SALES () SELECT Sum(Quantity) WHERE ItemNum = 10, then DELETE all the older records WHERE ItemNum = 10
But how to Delete rows but must maintain the new recort just inserted?

Or is there any good example or suggestion?

How to write that SQL?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-02-17 : 08:53:54

Why delete the record? Just return the sum of the records to your reporting application.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-17 : 20:25:37

I thought of that too, but the records can be modified anytime, including update the quantity and delete the row, so using sum or view will be very difficult to mananage. example:

If now I have two ItemNum 10, and using Sum I will have 200 in Quantity.

SalesID| ItemNum| Quantity
1 | 10 | 100
2 | 10 | 100
3 | 20 | 300

using Sum on view I will get the following output:
ItemNum | Quantity
10 | 200
20 | 300

Which is fine if I wanted the display the record for read only. But my records are updatable by users.

Scenario #1:
User wanted to Update ItemNum 10 to quantity 150.
- I need to minus 50 from either record.

Scenario #2:
User wanted to Update ItemNum 10 to quantity 50.
- I need to minus 100 + 50 from both records.

Scenario #3:
User change ItemNum 20 to 10.
- Now ItemNum 10 will have sum of 500

So I think using Sum and View will be not a good idea here...

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-17 : 20:59:26

No one query or view can solve this. You need to create a stored procedure. Inside it, you would have T-SQL statements that decided when to UPDATE, INSERT, or DELETE based on parameters passed from the GUI actions taken by the user. I would have a parent stored procedure call a separate stored procedure for each needed DML action. This way, the parent stored procedure does the deciding, and the other stored procedures manipulate the data. For starters, see:

http://msdn.microsoft.com/en-us/library/ms187926.aspx

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-17 : 21:16:04

I see. I'll look into this. Thanks for the tips

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources