SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Insert where PK doesn't exist

    Guys,

    I need to run through a simple table as follows:

    userid (int) (PK)
    setting (varchar(50)) (PK)
    value (varchar(50))

    I need to add the setting 'scentre' and value 'checked' for each distinct userid where this row doesn't already exist. I'm sure it's a really simple query but I can't for the life of me work it out

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Do the insert. If the row already exists, an error will be thrown. Ignore it.

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, and the best way to do that is to...?

    I want to do it all in one query. Can I do a sub-query from the same table in the userid insert?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Sorry, I didn't understand your question correctly.
    Try this
    Code MySQL:
    INSERT INTO tablename
    SELECT 
        a.userid
      , 'scentre' 
      , 'checked'
    FROM tablename a
    LEFT OUTER JOIN tablename b
    ON  a.userid = b.userid
    AND a.setting = b.setting
    AND b.setting = 'scentre'
    WHERE b.userid IS NULL

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    put the keyword IGNORE between INSERT and INTO. this will cause the duplicates to silently fail.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks to you both. I was wondering how to deal with the duplicate key error. Brilliant, thanks. Learn something new every time


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
  •