| Author |
Topic |
|
cesark
Posting Yak Master
215 Posts |
 Posted - 2005-06-22 : 12:11:16
|
| In my table ‘Products_by_customer’ I have 8 rows of a customer and a product (where every row is specifying a quality of the product and other features. But now I only want to specify 4 qualities with its features (so, only 4 rows) for that product. What I have to do to ‘update’ the table to the new customer product specifications? Update the first 4 old rows to my new 4 rows and delete the last 4 rows? Or add 4 new rows in the table and delete the old 8 rows? Or save the rows id in the application, then with these id’s update only the changed ones, insert the new ones and delete the rest? In my case I would have to update the id’ s 1, 7 and 8, insert one new row (the new quality C, small), and delete the rows 2, 3, 4, 5 and 6. Products_by_customer Table: (With 8 rows)Id, Customer_num, Product_num, Quality, size, price1, 25, 7, quality A, big, 20 2, 25, 7, quality A, medium, 16 3, 25, 7, quality A, small, 104, 25, 7, quality B, big, 185, 25, 7, quality B, medium, 146, 25, 7, quality B, small, 97, 25, 7, quality C, big, 158, 25, 7, quality C, medium, 12Products_by_customer Table: (New situation, with 4 rows, I don’ t need the rest)Id, Customer_num, Product_num, Quality, size, price1, 25, 7, quality A, big, 23 2, 25, 7, quality C, big, 18 3, 25, 7, quality C, medium, 134, 25, 7, quality C, small, 11 Which is the normal solution in these cases?Thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 18:51:49
|
| I would delete the data, then insert what you want.Tara |
 |
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-06-23 : 05:25:06
|
Yeah! Thank you,  The example I posted indeed is a customer sell ordering process (an offer in a website). So, as in the example, the user first want to sell 8 lines of product, but before confirm all the data she/he return to the previous form and decide to sell only 4 lines of product. After confirm all the data, this offer will be definitive, (I hope). At a first moment I also though that delete all the old ones and then reinsert them it was the best solution in my case. Anyway, I was told that doing this (delete all the rows of a current ordering process, to reinsert them again) was crazy, or not logic, and things like that. And I spent many many hours thinking in a way to update only the changed ones, insert the new ones and delete the rest, and other alternatives to delete as less rows as possible, but it is really very complicate in my case. And I don’ t think that DELETE ALL and REINSERT again will have a bad performance or will have problems in the future. |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-06-23 : 10:30:34
|
| So, could I develop this logic?:Every time the user presses a submit button in the app (back or forward in the form) first of all trigger a Stored Procedure to delete all the rows related with the current ordering process. If there is no rows yet in the table because is the first time the user send data for the current offer, the SP will not find any row with the current ordering process and nothing will happen. After that, make the insertion normally every time. Is this a good system?Thanks |
|
|
|
|
|
|
The best way to update several rows