SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    REPLACE or DUPLICATE KEY UPDATE

    Hi all

    Which is the better approach when you want to upload a chunk of info to a table but want it to overwrite rows with a certain unique key - REPLACE or INSERT with DUPLICATE KEY UPDATE?

    (I should explain - client will be uploading a .csv with the info in - they will add info to the .csv but want to be able to just re-upload the whole thing as and when required rather than just upload new rows)

    Somehow replace doesn't feel right... feels dangerous!

    This is what I've got - and it works fine:

    PHP Code:
    ... uploading .csv file ...
    ... 
    connect to db ...

    $filename='/home/public_html/uploads/users.csv';
    $handle fopen("$filename""r");
    while ((
    $data fgetcsv($handle1000",")) !== FALSE)
         {
          
    $import="REPLACE users (id,titlex,first_name,last_name,company,groupx,password) values(' ','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
          
    mysql_query($import) or die(mysql_error());
         }

    fclose($handle); 
    (yes, the password won't be in clear text in the db - this is just a simplified version.)
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i think you can answer that question yourself...

    if you were to use INSERT ON DUPLICATE KEY UPDATE, which column(s) would be duplicated, which column(s) would you update, and with what?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy.

    At first glance they appear to do roughly the same thing (in the end). Replace where duplication is found and otherwise just insert?

    I think that Replace deletes first and would therefore be slightly slower (not such a big chunk of info that that matters).

    It appears from my experiments that REPLACE is incrementing the id...

    The info from the .csv file has no id column. I'm adding that at the point where it's added to the db. I've made that the primary key. There is another unique field - password. So I could do insert on duplicate key update using the password as a unique key.
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    and which column(s) would you update?

    and if REPLACE increments the id, is it really replacing a row?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The REPLACE query is incrementing the id and it is replacing the current contents where it finds a duplication in the password which is unique.

    Are you saying with the update query that it would try to find a difference in a certain field and would only update that row so that if the password which is unique hasn't changed but the client has changed another field in that row - it wouldn't update?

    thanks for bearing with me!
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, i'm not saying that

    have you tested both commands?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haven't tested the update one yet.
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,786
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Replace will actually delete and reinsert the data while the on duplicate key update version will actually update the existing data.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

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