July 11, 2008 at 7:23 am
Hi,
I've been asked to create a mechanism to partially update a table. All the fields of the table are shown in the user interface of the program, but when the "update" button is clicked, only those fields that have changed, may be updated in the database.
The reason for this, is that it's a distributed application, with concurrent users, who might update a record at the same time.
For example: a table with fields "id", "name", "first name", "adress", "location".
User 1 changes "name" of record 1, and clicks "update".
User 2 changes "adress" of record 1, and clicks "update" at the same time.
2 update statements have to be executed, but for user1 only the field "name" may be updated, for user2 the field "adress".
Generation of these 2 statements is as easy as pie, but ...... i want to use a stored procedure to do this .... which makes it a bit harder.
Any tips are welcome !
Thanks in advance
Grtz
Fred
_________________________________________
Baller
July 11, 2008 at 8:05 am
Generally, I've just passed all the fields back to the proc and updated them. Depending the app & the business needs, it usually doesn't matter if FieldX is updated from 'Val1' to 'Val1'
However, if I was very concerned about it, I might try this:
UPDATE dbo.TableA
SET Col1 = COALESCE(@MyVal, Col1)...
Then pass only the values that have changed, nulling all the other parameters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2008 at 8:16 am
Coalesce(...) of course ! 😀
Generally i'd also pass all the values and update them, but this functionality was explicitly asked.
The motivation was that user2 would not overwrite the "name" change of user1, because users2's gui hasn't been refreshed in time.
But your solution is a simple and nice one, thanks !
_________________________________________
Baller
July 11, 2008 at 8:22 am
That's probably not going to do what you want. You are trying to build a concurrency model. You need to determine if the field has changed since you originally retrieved the record.
Typically, this would be a function of the UI or data access layer. ADO, for instance, knows is a field has been updated or not and if you use the .Update method, it only updates fields that actually were changed. This is pretty easily managed in the UI or data access layer because you can keep the original version of a field in the recordset and do the comparison with the value in the control when the update button is clicked.
To do this at the database level, you either need to keep track of the version of the record when the UI requested it (somehow) and compare this to the version doing the update, or you need to actually use one of the built in concurrency models.
July 11, 2008 at 8:26 am
Thanks for the reply, but the former solution will work better for us.
We work in a distributed environment, with webservices, and users at different countries.
We also work asynchronous, which makes it harder i guess.
Whether the data has changed or not, is tracked in the user interface. But when we know it has or haven't been changed, we'll need to pass "NULL" to our stored procedure or a real value.
When we pass NULL, the coalesce function will use the current value to fill in with the update. When we pass a value other then NULL, the new data will be written.
_________________________________________
Baller
July 11, 2008 at 8:31 am
As I look more carefully at Grant's solution, it is determining that the field changed in one of the application layers (setting the value to NULL if it has not been changed).
That should work fine.
July 11, 2008 at 8:37 am
That's my guess too 🙂
Thanks for the replies, guys !
_________________________________________
Baller
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply