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.
| Author |
Topic |
|
cesark
Posting Yak Master
215 Posts |
 Posted - 2005-05-24 : 10:33:47
|
In a web form, in the application, the user can go back after fill several fields and then return to that form page and modify the value of the fields and selections. But to modify the data in the DB is more complicate, because the inserted data which must be modified are divided in several rows in two tables, and the number of inserted rows is variable depending on the user selections every time, and I don’ t know how to ‘update’ those tables to the new user selections and filled fields. One table ‘Offer_quality’ is to store the data corresponding to the quality of the product which can be from one up to three different qualities, so from one up to three possible rows in the table (corresponding to an offer). The second table ‘Offer_ripening’ is to store the data corresponding to the ripening grade of the product which can be from one up to three different ripening grades for every quality (row) in ‘Offer_quality’ table, so this table could store from one up to nine rows in one insert process (corresponding to an offer).The tables:CREATE TABLE [Offer_quality] ( [Offer_quality_id] [bigint] IDENTITY (1, 1) NOT NULL , [Offer_num] [bigint], (FOREIGN KEY) linked to ‘Offers’ main table [Quality_num] [smallint], (FOREIGN KEY) (Q, QA, QM or QB) [Caliber] [numeric](6, 2) NULL , [Measure_Caliber] [smallint] NULL , (FOREIGN KEY) [Kind_Caliber] [smallint] NULL , (FOREIGN KEY) [Image_name] [varchar] (256) COLLATE Modern_… NULL , [Directory_path_num] [int] NULL , (FOREIGN KEY) [Transport_cost] [smallint] NULL , (FOREIGN KEY), CREATE TABLE [Offer_ripening] ( [Offer_ripening_id] [bigint] IDENTITY (1, 1) NOT NULL , [Offer_quality_num], (FOREIGN KEY) linked to ‘Offer_quality’ table [Ripening_grade] [smallint], (FOREIGN KEY) (MA, MM, MB or null) [Available_quantity] [numeric](8, 2) NULL, [Measure_quant] [smallint] NULL, (FOREIGN KEY) [Kind_container] [smallint] NULL , (FOREIGN KEY) [Quant_container] [numeric](8, 2) NULL , [Measure_quantCont] [smallint] NULL , (FOREIGN KEY) [Price] [numeric](8, 2) NULL , [Measure_Price] [smallint] NULL , (FOREIGN KEY), How can I ‘update’ these tables to the new user selections every time the user changes his/her mind?Thank you,Cesar |
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-24 : 11:41:28
|
| The problem I have is how to determine the rows I have to update or delete. For example the user in her/his first insert has inserted three rows in ‘Offer_qualtity’ table and five rows in ‘Offer_ripening’ table. And later when the user prefers to make another form selection, the consequence of the new selection in the DB is for example one only row in the ‘Offer_quality’ table (with new data), and three rows in the ‘Offer_ripening’ table (with new data). So, perhaps the best choice is to delete all the rows of the current offer (in both tables) every time the user send the data to the DB, and make a new insert every time. Thus if these two tables aren’ t filled yet with data of the current offer, the ‘delete’ statement will not have any effect, and if are already filled the rows of the current offer will be deleted and a new insert will be done. It would be a good solution? Or there are other better alternatives? |
 |
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-25 : 04:17:05
|
| I only want to know if it is a normal practice delete rows and insert them again to update tables in cases like mine, or if exists other better alternatives. Somebody can tell me its opinion please?Thank you |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-25 : 10:59:47
|
Hey! Such a difficult question is?  |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-26 : 06:37:46
|
| The only thing I am trying to guess is how can I update a set of inserted records that I don’ t know how many they are, because the user in his/her first insert has stored 1, 2 or 3 records (I don’ t know how many), but the user change his/her mind and later want to store 1, 2 or 3 records (I don’ t know how many). So, perhaps in the first insert there are 3 records, but the user change her/his mind and later only want to store 1 record, so as maximum I could update (I don’ t know how) one of these 3 records, the other two are no longer valid. So, I ask: In this scenario, is it a good choice delete all the records inserted of the current offer (Offer_num column) and then insert the new records? And do the same with the records inserted in the child table ‘Offer_ripening’ also of the current offer the user are ‘updating’?I ask this because I don’ t know which is the normal way to update tables with repeating rows. Another example could be: If a user want to sell one hat, the table will have one row with the attributes of that hat, but if a user want to sell four hats the table will have four rows every one with the attributes of every hat. So, if during the process to introduce the offer, the user change de decision to sell six hats and later decide to sell 3 hats, how can I update this change in the DB?Some opinions please.. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-26 : 08:14:39
|
| There are a few considerations here:1. You never want to just delete and reinsert EVERYTHING. That's crazy and will result in some really messed up indexes, a lot of contention, and generally just bad performance. This is especially true if it's a high transaction system.2. You probably, in most instances, want to ONLY delete, insert, or update the data necessary. You might want to consider comparing the data between submits at the client/app server level and only passing the necessary calls back to the database. This would be the case more in a true client/server side app as traffic becomes more important in this scenario.3. What are the business requirements. Do they need to see only what the user committed at the end of the entire transaction? There are some cases where they might want to see the variations of what he chose, then have deleted/updated history to show what was changed during the lifecycle of the customers ordering process. This will be more overhead and storage for the system; however, it could provide valuable metrics to the business if handled properly. It's just something to think about. :) MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-26 : 11:10:50
|
| First of all thank you very much to answer. I am not a DB expert, but I am very surprised that nobody knows (also in other forums) how to update a table with repeating rows, something that is necessary in many cases, I suppose.. My ASP.NET VB.NET web application has some processes that consists in two or three pages of web forms, I mean that to complete a transaction the user has to complete two or three web form pages. While the user is in one of these processes, every time he/she press the ‘next’ or ‘previous’ submit button the data is stored in the DB, and if she/he decide to go back and forward through these submit buttons several times rethinking the data to write in these form pages, simply the data in the DB is updated every time. This is a good way to save server memory because you don’ t have to store a lot of data of many users in server memory at the same time before confirm the whole process. This system also has other advantages, if during the process something fails the data is already stored in the DB, the amount of data to insert/update every time is small therefore faster and simple, etc.. I think it’s a very good design, and a lot of web sites uses this technique. Until now there wasn’ t any problem because I only had to update a single record (row) every time in the tables, but now I have to update tables with repeating rows and I don’ t know how to do it. I think that it must be a simple technique to do so, since a lot of web sites uses databases with tables with repeating rows. For example a table that contains curriculum of users must have repeating rows, because a user may have more than one job experience, so, this table will contain several records (rows) of the same user with a job experience in every one. Then, when the user want to update his/her curriculum (adding, updating or deleting), how the database administrator or developer have to handle this update? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-26 : 11:34:26
|
| Read my post. It's not our lack of knowledge. It's your lack of comprehension.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-26 : 12:42:34
|
| Maybe yes.1. In your first point, You mean that I can delete some rows form time to time, only the necessary ones, but I can’ t delete rows as a system to update repeating rows in tables? 2. In your second point, I understand that in my case I have to detect between submits the records to delete and the records to update, and not delete all the records to reinsert the new ones. Is this correct?3. In your three point, You mean if I need a way to tell the user what changes has made during the process? If so, I don’ t need this service. Is all what I have understood correct? If so I have something important to start with. |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2005-05-26 : 19:03:42
|
| Well.. I see that isn’ t a problem of my comprehension because nothing it’s clear. Anyway thank you to give your opinions about that mysterious issue, I hope some day I discover the simple or complex answer, and why all this silence about this question. |
|
|
|
|
|
|
|
|
Trying to update several rows in two tables