SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Avoiding duplicate entries (MySQL)

    I haven't started this yet so all suggestions are welcome.

    col_1 = integer between -180 and +180
    col_2 = integer between 3000 and 12750
    col_3 = integer between 1000 and 45000
    col_4 = integer between 0 and 3
    col_5 = timestamp(now)

    I'm receiving data from a central server that I don't control and it must put it into a local MySQL database, but without any duplicates. A duplicate is when columns 1, 2, 3, and 4, are all identical to the incoming data. In that case I just want to update the timestamp, not insert a new line. If only 3 or less of the first 4 columns are matches I want to insert a line. What is the best way to go about this?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,061
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Make col_1 through col_4 the primary key and use INSERT ... ON DUPLICATE KEY UPDATE
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Make col_1 through col_4 the primary key and use INSERT ... ON DUPLICATE KEY UPDATE
    How can you make 4 columns a primary key? I thought a primary key was on one column, not a combination of columns.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,061
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    No, you can use multiple columns if you like.

    Something like

    Code:
    CREATE TABLE mytable (
       col_1 INT,
       col_2 INT,
       col_3 INT,
       col_4 INT,
       col_5 INT,
       PRIMARY KEY (col_1, col_2, col_3, col_4)
    )
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    From the manual:
    A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
    What he says ^^

  6. #6
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks


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
  •