MySQL Forums
Forum List  »  InnoDB

Conditional insert (INSERT WHERE NOT EXISTS )
Posted by: kobus smit
Date: July 27, 2007 05:10AM

i have a table :

CREATE TABLE `bnum` (
`boat_num` varchar(10) NOT NULL,
`lname` varchar(25) default NULL,
`used` tinyint(1) default NULL,
PRIMARY KEY (`boat_num`),
KEY `bn_fk` (`lname`),
CONSTRAINT `bnum_ibfk_1` FOREIGN KEY (`lname`) REFERENCES `sofficer` (`lname`)
);

and i need to insert a range of values if that specific value does not exist. but sql doesnt work, ive come to the conclusion that the conditional insert doesnt work well in mysql

//PHP code I use in the php file, i used different versions of the $query with and without $num i even tried NOT IN

for($i =0;$i<=$num;$i++)
{
//echo $start+$i;
$num = $start + $i;
$query = "INSERT INTO BNUM VALUES(CONCAT(".$start ."+".$i.")" .",'".$lname."',0) WHERE NOT EXISTS (SELECT * FROM bnum WHERE boat_num = '".$num."') ; ";
$result = mysql_query($query) or die('Error in query $query.' .mysql_error());
//mysql_flush($result);
}

is it possible to have a conditional INSERT? if not how should I go to do the same without the condition.
im using mysql 5.0

Options: ReplyQuote


Subject
Views
Written By
Posted
Conditional insert (INSERT WHERE NOT EXISTS )
143700
July 27, 2007 05:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.