Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Red face Unanswered: Update statement design

    I have to write all my update statements for a new app i'm starting, in the past I would just update all values where the id= correct id and timestamp= initial timestamp.

    This application I have to record all changes in a log table. So I am thinking I should only update fields that actually changed. If I have an address table, with fields:

    house#,
    apt#
    street,
    city,
    state
    zip

    How would I design an update statement? The user could be updating only 1 field, all, or any number of combinations of any fields. Would I do an update statement for each scenario? Naaaa so what do I do?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Maybe it's just me, but I don't see why updating all the fields is not acceptable.
    Updating a field with the same value is effectively not updating anything....
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    I have looked into it more and realize its no big deal,, thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    We have some applications where we have a "last changed" timestamp for each row in a table, and we only want to update if a value we are about to update is different than the value currently in the row/column.

    What we do in most cases for that is just do the update to update everything at once, but with a WHERE clause that compares each column value with those we are about to update, and if they are all the same, we don't do the update(s). It can get ugly in the code depending on the number of columns (since there needs to also be checks for NULL), but in some cases I just use BINARY_CHECKSUM to compare them all at once.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •