1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Replace Statement in MySQL

Discussion in 'MySQL' started by pradeep, Feb 21, 2008.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Many times we need to update a record but we might not know whether such a record exists or not. In traditional programming we usually check whether the record exists or not, then perfrom the appropriate query according to the result of the previous query. A very good example of such situatuion is where you need to update the ip of an user against an user id, but the id may or may not be present in the visits table, user_id being the primary key for the table visits.

    An usual solution to the problem is something like this.

    Code:
     my $user_id = 1000;
     my $ip = $ENV{'REMOTE_ADDR'};
     my $stmt = $db->prepare('SELECT * FROM visits WHERE user_id=?');
     $stmt->execute($user_id);
     
     if($stmt->rows) ## a entry for the page id exists, update the record
     {
         my $update_stmt = $db->prepare('UPDATE visits SET ip = ? WHERE user_id=?');
         $update_stmt->execute($ip,$user_id);
     }
     else ## no such id, insert a new entry with count 1
     {
         my $insert_stmt = $db->prepare('INSERT INTO visits VALUES(?,?)');
         $insert_stmt->execute($user_id,$ip);
     }
     
    PHP:
     $user_id 1000;
     
    $ip $_SERVER['REMOTE_ADDR'];
     
    $res mysql_query("SELECT * FROM visits WHERE user_id=$user_id");
     
     if(
    mysql_num_rows($res)) // a entry for the page id exists, update the record
     
    {
         
    $result mysql_query("UPDATE visits SET ip = '$ip' WHERE user_id=$user_id");
     }
     else 
    // no such id, insert a new entry with count 1
     
    {
         
    $result mysql_query("INSERT INTO visits VALUES('$user_id','$ip')");
     }
     

    Enter: REPLACE



    The REPLACE statement very similarly or exactly like INSERT statment, except for the fact that in case a record with the same primary key exists in the one that's going to be inserted then the old row is deleted before the new one is inserted. You need to have an unique index or a primary key to use REPLACE statment.

    REPLACE is not part of the SQL standard, it's an useful extension by MySQL, I guess there are many other RDBMSs which have come up with similar extensions.

    So, now that we have REPLACE, we can rewrite above code snippets in the following manner:

    Code:
     my $user_id = 1000;
     my $ip = $ENV{'REMOTE_ADDR'};
     my $stmt = $db->prepare('REPLACE INTO visits VALUES(?,?)');
     $stmt->execute($user_id,$ip);
     
    PHP:
     $user_id 1000;
     
    $ip $_SERVER['REMOTE_ADDR'];
     
    $res mysql_query("REPLACE INTO visits VALUES('$user_id','$ip')");
     
    That's it, isn't that easier guys!!

    Read more about REPLACE statement here, http://dev.mysql.com/doc/refman/5.0/en/replace.html

    Alternative Approach



    A forgotten or unknown feature among the masses is the ON DUPLICATE KEY UPDATE, it causes an UPDATE when a duplicate key error is encountered during an insert. The above code snippet again be re-written like this,

    Code:
     my $user_id = 1000;
     my $ip = $ENV{'REMOTE_ADDR'};
     my $stmt = $db->prepare('INSERT INTO visits VALUES (?,?)  ON DUPLICATE KEY UPDATE ip=?');
     $stmt>execute($user_id,$ip,$ip);
     
    PHP:
     $user_id 1000;
     
    $ip $_SERVER['REMOTE_ADDR'];
     
    $res mysql_query("INSERT INTO visits VALUES ('$user_id','$ip')  ON DUPLICATE KEY UPDATE ip='$ip'");
     
    You can read more about this feature here, http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
     
  2. arbaz.it

    arbaz.it New Member

    Joined:
    Feb 13, 2008
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    0
    good information
     
  3. arbaz.it

    arbaz.it New Member

    Joined:
    Feb 13, 2008
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    0
    can you please submit some more tough examples
     

Share This Page