SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL INSERT only if entries don't exist

    I have a quick question:

    I have a table called mytable that has the following structure:

    Code MySQL:
    id INT NOT NULL AUTO_INCRMENET PRIMARY KEY
    vert_id INT NOT NULL
    local_id INT NOT NULL

    With the following rows:

    Code MySQL:
    id---vert_id----local_id
    1       10             6
    2       52             6
    3      67              8


    I want to run an insert like this:
    Code MySQL:
    INSERT INTO mytable (vert_id, local_id) VALUES ('10', '6'), ('10', '7')

    I need mysql only to insert the ('10', '7') and not ('10', '6') because that pair already exists.

    I tried using insert ingore, but since these aren't unique or primary indexes it doesn't work.

    Any help would be greatly appreciated
    thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jsbarra View Post
    ... since these aren't unique or primary indexes
    why aren't they? sounds like they should be
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because the pairs need to be unique, for instance having multiple rows that have a vert_id of 10 or having multiple rows with a local_id of 6 is okay, but having multiple rows with a vert_id of 10 and a local_id of 6 is not okay,

    I am pretty new at this stuff so i may not be phrasing it correctly. Chances are I am going about this all wrong

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An index can span multiple columns.

    create unique index vert_local on mytable (vert_id, local_id)

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah ha! thanks crmalibu! You seem to always help me out thanks a lot!!


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
  •