Post reply

Updating a table

  • 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.

  • October 21, 2010 at 11:57 am

    #1240491

    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!

  • October 21, 2010 at 12:13 pm

    #1240499

    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?

  • October 21, 2010 at 12:23 pm

    #1240507

    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!

  • October 21, 2010 at 1:45 pm

    #1240565

    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.

  • October 21, 2010 at 1:57 pm

    #1240568

    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!

  • October 21, 2010 at 2:01 pm

    #1240573

    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

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

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