sabecs
asked on
MySQL - insert rows into table where no matches found in column?
Hi,
I want to insert new rows into my table, but only if the icon_name does not already exist.
What is the best way to do this?
Thanks in advance for your help.
INSERT INTO `icons` (`icon_name`) VALUES ('page one');
INSERT INTO `icons` (`icon_name`) VALUES ('help page');
INSERT INTO `icons` (`icon_name`) VALUES ('another page');
CREATE TABLE IF NOT EXISTS `icons` (
`id` tinyint(3) NOT NULL auto_increment,
`icon_position` tinyint(3) default '100',
`icon_name` varchar(120) NOT NULL,
`visible` enum('1','0') default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `icons`
--
INSERT INTO `icons` (`id`, `icon_position`, `icon_name`, `visible`) VALUES
(1, 0, 'was one', '1'),
(2, 4, 'help page', '1'),
(3, 1, 'was three', '0'),
(11, 100, 'new_webpage', '1');
I want to insert new rows into my table, but only if the icon_name does not already exist.
What is the best way to do this?
Thanks in advance for your help.
INSERT INTO `icons` (`icon_name`) VALUES ('page one');
INSERT INTO `icons` (`icon_name`) VALUES ('help page');
INSERT INTO `icons` (`icon_name`) VALUES ('another page');
CREATE TABLE IF NOT EXISTS `icons` (
`id` tinyint(3) NOT NULL auto_increment,
`icon_position` tinyint(3) default '100',
`icon_name` varchar(120) NOT NULL,
`visible` enum('1','0') default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `icons`
--
INSERT INTO `icons` (`id`, `icon_position`, `icon_name`, `visible`) VALUES
(1, 0, 'was one', '1'),
(2, 4, 'help page', '1'),
(3, 1, 'was three', '0'),
(11, 100, 'new_webpage', '1');
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add PRIMARY_KEY to the table field:
http://www.w3schools.com/sql/sql_primarykey.asp
http://www.w3schools.com/sql/sql_primarykey.asp
ASKER
Thanks for your help.
create table t1 (
f1 integer primary key,
f2 varchar(50) unique not null
)