SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert if no duplicate

    is there a way to do that? insert a new row into my database, if there isnt already one with the exact same data. for example if this already exists

    id - user_id - ip
    3 - 123 - 22.22.2222

    and someone tries to submit

    user_id: 123
    ip: 23.23.23333

    it will insert a new row, and not replace the original

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    INSERT
      INTO daTable
         ( user_id 
         , ip )
    VALUES
         ( 123
         , '23.23.23333' )
    ON DUPLICATE KEY 
    UPDATE ip = VALUES ( ip )
    this depends on a unique index on user_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ON DUPLICATE KEY
    UPDATE ip = VALUES ( ip )

    what if i didnt want to update the old one, so something like

    ON DUPLICATE KEY
    DO nothing

    and what does KEY refer to? would that check only the id value?

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by franco714 View Post
    what if i didnt want to update the old one
    then you would use INSERT IGNORE

    Quote Originally Posted by franco714 View Post
    and what does KEY refer to?
    any unique key

    check da manual for a full explanation

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


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
  •