Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Identical UPDATE?

    Hello forum,

    I am just wondering what a DB reacts (or should react) to an UPDATE statement that includes an identical update. By this I mean updating a column with exactly the same value as what is currently found.

    Code:
    UPDATE SomeTable
       SET col_1 = col_1
    Does the database actually update the column with the self-same new value or is it clever enough to skip execution, "Oh, the new value is the same as the old one. No need to start an update for this row. Next!"?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql is smart enough, not sure about other database systems

    not sure about ANSI SQL either (the forum you posted in)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    Thanks rudy. I was actually thinking of branching update values using CASE:
    Code:
    UPDATE SomeTable
       SET col_1 = CASE WHEN <cond 1>
                        THEN  col_1 * 10 
                        WHEN <cond 2>
                        THEN  col_1 * 5 
                   ELSE col_1 END
    I wondered if writing "ELSE col_1" makes the execution considerably slow compared to a statement that has "<cond 1> OR <cond 2>" in a WHERE clause. Glad to know that I don't have to worry about it for MySQL. Still, I will wait for info about "da standard" and other DB products.

    By the way, what if the ELSE condition were "col_1 * 1"? Maybe SQL is not very good at processing equations?

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    What about transaction isolation levels and write-sets?

    Example:
    You have a table with one million rows, where 10 rows fulfill <cond 1>
    and 10 other rows fulfill <cond 2>.

    If the UPDATE statement has no where clause the transaction involves all 1 000 000 rows. What do you want to happen if somebody else at the same time updates one of the 999 980 "else" rows?

  5. #5
    Join Date
    Mar 2006
    Posts
    56
    I have a more rudimentary case where the data size is under 100 and the change is more or less permanent, but I see your point. THanks.

Posting Permissions

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