Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

MySQL Insert Where not exists

Cabbit

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,128
1
Scotland
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
 
colocolo

colocolo

macrumors 6502
Jan 17, 2002
480
132
Santiago, Chile
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.
 
Cabbit

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,128
1
Scotland
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.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom