Conditional insert (INSERT WHERE NOT EXISTS )
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
Subject
Views
Written By
Posted
Conditional insert (INSERT WHERE NOT EXISTS )
143700
July 27, 2007 05:10AM
83225
July 27, 2007 06:49AM
48768
July 28, 2007 09:57AM
27741
March 25, 2009 12:31PM
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.