SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with IF NOT EXISTS query

    Hi

    I've been looking around all over the internet for the right syntax for IF NOT EXISTS but can't seem to get it right.
    I've got this at the moment:
    Code:
    INSERT INTO user_sites (userid, siteid) VALUES ('10','39') WHERE NOT EXISTS (SELECT * FROM user_sites WHERE userid = '10' && siteid = '39')
    but it comes up with an error each time.

    Can anyone please help!?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    3 Thread(s)
    I don't think you can use NOT EXISTS in an INSERT query, only in SELECT queries. Please read this.
    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
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sheardben View Post
    Hi

    I've been looking around all over the internet for the right syntax for IF NOT EXISTS but can't seem to get it right.
    I've got this at the moment:
    Code:
    INSERT INTO user_sites (userid, siteid) VALUES ('10','39') WHERE NOT EXISTS (SELECT * FROM user_sites WHERE userid = '10' && siteid = '39')
    but it comes up with an error each time.

    Can anyone please help!?
    I think
    INSERT ... ON DUPLICATE KEY UPDATE Syntax may be appropriate for you.
    Reference:
    http://dev.mysql.com/doc/refman/5.0/...duplicate.html

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    I think
    INSERT ... ON DUPLICATE KEY UPDATE Syntax may be appropriate for you.
    Reference:
    http://dev.mysql.com/doc/refman/5.0/...duplicate.html
    Thanks, but i'm not sure if that will work in my case - there is no key in this table because it's just storing the sites that a user has added to their account.

    It's not too serious - i can do a query before and check whether rows are returned and if not insert, just wanted to avoid running two queries if i could do it with one.

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not too serious - i can do a query before and check whether rows are returned and if not insert, just wanted to avoid running two queries if i could do it with one.
    used for the same case btw

  6. #6
    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 sheardben View Post
    there is no key in this table because it's just storing the sites that a user has added to their account.
    do you allow the same user to add the same site more than once?

    if the answer is no (and judging by the query you're trying to run, this is exactly what you want), then you should have a unique key on that pair of columns

    thus INSERT... ON DUPLICATE KEY UPDATE will work

    another option for you is INSERT IGNORE and i'll let you look that one up in da manual to see whether it might suit this particular scenario (hint: yes)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aha - thanks very much, that has solved it completely! I'm quite new to all this and i've seen the unique key option, but never knew exactly what it was all about. Just been checking it out now - didn't know that you could set it over multiple columns.

    INSERT IGNORE is working perfectly now, and i've created the unique index over the columns.

    Thanks so much!


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
  •