MySQL Insert Where not exists

Discussion in 'Web Design and Development' started by Cabbit, Nov 25, 2009.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    Code:
    INSERT INTO `blogTags` (`parentID`, `tagID`) VALUES('3', '2') WHERE NOT EXISTS (SELECT * FROM `blogTags` WHERE `parentID` = '3' AND `tagID` = '2' )
    Working on this query here it should add in a new record into blogTags if the parentID and tagID(compound key) do not already exist. However it returns the following error.
    Exception code: 1064
    Exception message:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM `blogTags`
    WHERE `parentID` = '3' AND' at line 3
     
  2. colocolo macrumors 6502

    Joined:
    Jan 17, 2002
    Location:
    Santiago, Chile
    #2
    The syntax is incorrect. You cannot insert where.

    Either put a select first to check if it exists, use "insert ... on duplicate key update" statement, or rewrite your code as to not put yourself in that situation.
     
  3. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    replaced with

    Code:
    INSERT INTO `blogTags` (`parentID`, `tagID`) SELECT '$postID', '$overID' FROM dual WHERE not exists (SELECT * FROM `blogTags` WHERE `parentID` = $postID AND `tagID` = $overID );
    seems to work fine, just testing it a few times.
     

Share This Page