SQL Server Performance Forum – Threads Archive

Trying to update several rows in two tables

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#%92 t know how to â€˜update#%92 those tables to the new user selections and filled fields.
One table â€˜Offer_quality#%92 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#%92 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#%92 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#%92 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#%92 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#%92 these tables to the new user selections every time the user changes his/her mind? (in this case, when we want to update, it can exceed or lack rows) Thank you,
Cesar

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#%92 table and five rows in â€˜Offer_ripening#%92 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#%92 table (with new data), and three rows in the â€˜Offer_ripening#%92 table (with new data). So, perhaps the best choice is delete all the rows of the current offer (in both tables) before inserting every time the user send the data to the DB (press the submit button to go back or forward). It would be a good solution? Or there are other better alternatives?
Thank you,
Cesar

I only want to know if it’ s a normal practice delete rows and insert them again to update tables in cases like mine, or if exists other better alternatives. Suggestions? Thanks

The only thing I am trying to guess is how can I update a set of inserted records that I don#%92 t know how many they are, because the user in his/her first insert has stored 1, 2 or 3 records (I don#%92 t know how many), but the user change his/her mind and later want to store 1, 2 or 3 records (I don#%92 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#%92 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#%92 also of the current offer the user are â€˜updating#%92? I ask this because I don#%92 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..

Perhaps the solution could be: Detect between submits in the app the records to delete and the records to update, and not delete all the records to reinsert the new ones. Only delete the necessary ones. It would be this a good solution? Or this isn’ t a good way to update a table with repeating rows? Although I can’ t see another alternative..

Dear sir, it is definately help if the rows that are not changed is detected and change only those values or … plz try using INSERTED and DELTED tables value to do so.
thanx. hsGoswami
[email protected]

My preference is to use an UPDATE command since that’s what it’s there for – the other method you are talking about doesn’t sound like it’s going to save that much coding as you have to write an extra DELETE method anyway instead of the UPDATE and then reuse your INSERT. Under the hood, logically speaking anyway SQL Server does a delete/insert combo when you perform an update but it sounds like you’re sql code is coming from the client so probably two round trips would be involved in this command. I don’t really see what your problem is? Make sure that when displaying the form again for the user to update previously entered details that you provide in the web page the unique key to identify each row and then your UPDATE commands should be easy. Note: you will have to UPDATE each table seperately – you can’t update more than one table with one UPDATE command. The best solutions normally are the most obvious ones, thus the most understandable and maintainable. Use an UPDATE on each table with the unique keys passed from the web page. Dave [email protected]
www.matiogi.com

Hi Dave, I appreciate very much your suggestion. And what you say is what I am looking for, but I don#%92 t really know how to do it.
My Sql code isn#%92 t coming from the client, I use SP inside Sql Server and commands in the web application to talk with the DB. You said:

quote: Make sure that when displaying the form again for the user to update previously entered details that you provide in the web page the unique key to identify each row and then your UPDATE commands should be easy.

I find your suggestion very interesting. You mean, when the web form is refilled â€˜automatically#%92 with the data previously entered by the user in the DB, I can return the row identities to the app so that I can update them or delete them accordingly the next time the user submit the form, comparing the current sent data with the returned identities. Is this what you mean? If so, I will try to think a way to do it. But the form page I am talking about is really complex. If I can#%92 t find a way, which can be possible, do you think that execute a SP to delete all the current inserted rows every time before making the new insert, is a bad way to update my tables with repeating rows?

Yes that was what I meant – if it’s possible try and get the identities sent back to the web form so you can subsuquently uniquely identify them for updating/deleting. Otherwise you’re going to be guessing which rows to modify. When you say you can delete all rows and insert all rows again, are you saying that you would delete all rows for this user only? If so, this solution is not the nicest – it’s going to be slower since more transactions are occurring, it’s going to cause more locks since deletes and updates hold exclusive locks, your database transaction log will grow far more quickly than normal due to the increase in transactions – if someone modifies just one entry you are going to end up modifying ALL of their data. Having said that, if this is the situation you’ve been given and there’s no method of getting the identities down to the web form then back to you again in the stored procedure then I don’t see you have much choice. I would try and make this clear to whoever your senior developer is though and let them know this solution, whilst it will work, will not be nearly as scalable as it could be. Dave [email protected]
www.matiogi.com

I am thinking in a logic that may work, tell me what do you think. When loading the web form to refill it with the current data in the DB, retrieve the identities of the inserted rows in both tables (in the current customer ordering process). With those identities on client side or in server side (session variable), use them in the next submit to compare with the number of rows now the user is going to insert, and delete or update accordingly. So, for example: If the number of returned identities of the first table are three, and now the user is going to insert only one row, I have to delete the last two rows (of the current customer order number) and update the first inserted row (of the current customer order number). In this way the order of the insertions will be correct (in the way the user has inserted them). And I will not have to guess which rows to update or delete according to the kind of data that contains (this is what I found almost impossible to achieve). Can you think in a simpler way to do it?

Yeah well I would figure on the web page you are probably going to have a delete button for any existing rows. What’s wrong with filling a hidden <input type="hidden" name="delete" value="123"> variable? Then there is zero guessing. That can be done with client side scripting to add new form variables to the page – remember you should still qualify your DELETE statement to include a WHERE userid = currentuser – to prevent users surrepticiously deleting other peoples data if they find out the code. You could do a similar thing with update. Dave [email protected]
www.matiogi.com

Ok, thank you

]]>