Updating a table

Question

Updating a table

camiloaguilar1

Say Hey Kid

Points: 667
More actions
October 21, 2010 at 11:46 am

#228219

Well, I have the following case.
I have a detail table like this.
Cod - Number - Product - Quantity - Price - Total
8 1 xxx 4 2 8
8 2 xxx 1 2 2
8 3 xxx 2 2 4
Cod, number and product number are primary keys.
I have to make from those 3 rows, 1 row like this....
Cod - Number - Product - Quantity - Price - Total
8 1 xxx 7 2 14
I have the sql query to get my normal rows, but don't now the next step, because of primary keys I just can't update individual rows.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well you didn't provide any examples of what was going wrong;

here's the data you posted in a consumable format, as well as a query that pulls the data you were asking for into the requested format...

why do you have to delete values and put others in place? why can't you simply use a view or a query (like below) to get the data, and leave the original raw data untouched?

/*--results

Cod Number Product (No column name) Price Total

8 1 xxx 7 2 14

*/

SELECT Cod,MIN(Number) AS Number,Product,SUM(Quantity),Price,SUM(Quantity) * Price AS Total

FROM

(SELECT '8' AS Cod,'1' AS Number,'xxx' AS Product,4 AS Quantity,2 AS Price,8 AS Total UNION ALL

SELECT '8','2','xxx',1,2,2 UNION ALL

SELECT '8','3','xxx',2,2,4 )

myTable

GROUP BY Cod,Product,Price

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 2

Because we have a lot of order details from a sale. So for no wasting of lot of order papers we want to make 1 order from all the others. So we have 3 orders with the same product. We want to have 1 order with one product updating the quantity and total price. So with that query I could retrieve the correct values I guess, but how can I delete and update with new current value?

Lowell SSC Guru Points: 323518 More actions · Answer 3

camiloaguilar1 (10/21/2010)
Because we have a lot of order details from a sale. So for no wasting of lot of order papers we want to make 1 order from all the others. So we have 3 orders with the same product. We want to have 1 order with one product updating the quantity and total price. So with that query I could retrieve the correct values I guess, but how can I delete and update with new current value?

exactly my point...as a DBA, i would never delete data and replace it with a rollup of information or anything without a good reason...I haven't seen that reasoning so far in your question.

it is very easy to get the data you requested in a compact format...so you don't waste paper as you mentioned.

what would happen if you deleted orders 2 and 3, and the customer called and said "i changed my mind about order 3, just send me 1 and 2"

since the data would no longer exist, (because you plan on deleting it) you cause a lot of trouble for yourself..

you need a much better reason for deleting orders than trying to save paper...i don't think you've looked at the business ramifications at all.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 4

I got that clear but I did not explain it that good, I create that table in temporal way. The goods are already with the client to so modifications would be needed. That was a special sale for products without movement. So we decide by the end of this sale to build just 1 orders for all.

Lowell SSC Guru Points: 323518 More actions · Answer 5

camiloaguilar1 (10/21/2010)
I got that clear but I did not explain it that good, I create that table in temporal way. The goods are already with the client to so modifications would be needed. That was a special sale for products without movement. So we decide by the end of this sale to build just 1 orders for all.

it's your data.

all i can do is advise you to make a backup before you do this.

here's an example:

--save the rollup info instead of creating a view

SELECT Cod,MIN(Number) AS Number,Product,SUM(Quantity) AS Quantity,Price,SUM(Quantity) * Price AS Total

Into #TMP

FROM

(SELECT '8' AS Cod,'1' AS Number,'xxx' AS Product,4 AS Quantity,2 AS Price,8 AS Total UNION ALL

SELECT '8','2','xxx',1,2,2 UNION ALL

SELECT '8','3','xxx',2,2,4 )

myTable

GROUP BY Cod,Product,Price

--delete the critical, important data against all advice

DELETE FROM myTable

--insert the rollup info

INSERT INTO myTable

SELECT * FROM #TMP

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 6

Lowell, sorry! I'll like to thank you a lot, it was very useful for me your help! I would not erase any data. I will create 1 order with a different number but including all products, so for history to keep that