locked
Partially update a table RRS feed

  • Question

  • 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

    Friday, July 11, 2008 1:03 PM

Answers

  •  Kent Waldrop Jl08 wrote:

    Are you talking about two users that are viewing / updating the same record from the same application windows on different terminals?  Fair enough.  So we have a stored procedure with the following arguments:

     

    (1) Id, (2) name, (3) first name, (4) address and (5) location.

     

    I assume that ID is a mandatory argument to the update and that the other arguments are optional.

     

    So for the first call to update name only, you would pass (1) id and (2) name and pass the other arguments as null?

    For the second update of address only, you would pass (1) and (4) address and pass the other arguments as null -- is that correct?

     

     

    Correct. And i already found a solution to my problem to (thanks to people on sqlservercentral.com Smile )

    I'll use the coalesce() function, with null-values for parameters which i don't want to update.

     

    Thanks though !

    Friday, July 11, 2008 4:14 PM

All replies

  • Are you talking about two users that are viewing / updating the same record from the same application windows on different terminals?  Fair enough.  So we have a stored procedure with the following arguments:

     

    (1) Id, (2) name, (3) first name, (4) address and (5) location.

     

    I assume that ID is a mandatory argument to the update and that the other arguments are optional.

     

    So for the first call to update name only, you would pass (1) id and (2) name and pass the other arguments as null?

    For the second update of address only, you would pass (1) and (4) address and pass the other arguments as null -- is that correct?

     

    Friday, July 11, 2008 2:22 PM
  • What you describe is a common problem with databases and is extremely complex to do unless your application can flag what is "different" when it submits the update.  Most applications implement "last one wins".  The 2nd update changes all the fields.

    Your example is simple.  However, take this for example:

    User 1 changes "name" and "address" and "city/state/zip" and click Update
    User 2 changes "address" and "city/state/zip" and clicks update

    Who wins?


    Worse. 

    User 1 retrieves the record, updates "name" and "address" and goes to lunch.
    User 2 updates "address" and "city/state/zip" and clicks "Update"
    User 1 returns from lunch and realizes they left the screen up and clicks "Update"

    You can spend all your time "trying" to stop this, but you have to assume the "last" update is the most current update to the record and go with it.


    Friday, July 11, 2008 2:22 PM
  •  Tom Phillips wrote:
    What you describe is a common problem with databases and is extremely complex to do unless your application can flag what is "different" when it submits the update.  Most applications implement "last one wins".  The 2nd update changes all the fields.

    Your example is simple.  However, take this for example:

    User 1 changes "name" and "address" and "city/state/zip" and click Update
    User 2 changes "address" and "city/state/zip" and clicks update

    Who wins?


    Worse. 

    User 1 retrieves the record, updates "name" and "address" and goes to lunch.
    User 2 updates "address" and "city/state/zip" and clicks "Update"
    User 1 returns from lunch and realizes they left the screen up and clicks "Update"

    You can spend all your time "trying" to stop this, but you have to assume the "last" update is the most current update to the record and go with it.


     

    You are correct, and i use the principle of "the last one wins". But to limit the fact that users overwrite each others updates, my customer wants me to use "last one wins" on a single field level, not on an entire record level.

    Which makes sense, imho.

    Thanks for the reply though.

    Friday, July 11, 2008 4:10 PM
  •  Kent Waldrop Jl08 wrote:

    Are you talking about two users that are viewing / updating the same record from the same application windows on different terminals?  Fair enough.  So we have a stored procedure with the following arguments:

     

    (1) Id, (2) name, (3) first name, (4) address and (5) location.

     

    I assume that ID is a mandatory argument to the update and that the other arguments are optional.

     

    So for the first call to update name only, you would pass (1) id and (2) name and pass the other arguments as null?

    For the second update of address only, you would pass (1) and (4) address and pass the other arguments as null -- is that correct?

     

     

    Correct. And i already found a solution to my problem to (thanks to people on sqlservercentral.com Smile )

    I'll use the coalesce() function, with null-values for parameters which i don't want to update.

     

    Thanks though !

    Friday, July 11, 2008 4:14 PM