my insert statement seems to be ignoring my unique index and adding duplicates
what'd i mess up?
thanks in advance
CREATE TABLE `address` ( `address_id` int(11) NOT NULL AUTO_INCREMENT, `address_street1` varchar(100) NOT NULL, `address_street2` varchar(100) DEFAULT NULL, `address_street3` varchar(100) DEFAULT NULL, `address_city` varchar(45) NOT NULL, `address_state` char(2) DEFAULT NULL, `address_zip` varchar(10) NOT NULL, `address_country` varchar(100) NOT NULL, `address_latitude` double DEFAULT NULL, `address_longitude` double DEFAULT NULL, PRIMARY KEY (`address_id`), UNIQUE KEY `a_dupe` (`address_street1`,`address_street2`,`address_street3`,`address_city`,`address_state`,`address_zip`,`address_country`) ) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;
set @address_street1 = '1 street name'; set @address_street2 = null; set @address_street3 = null; set @address_city = 'some city'; set @address_state = 'SS'; set @address_zip = '00000'; set @address_country = 'US'; INSERT INTO address (address_street1, address_street2, address_street3, address_city, address_state, address_zip, address_country) VALUES (@address_street1, @address_street2, @address_street3, @address_city, @address_state, @address_zip, @address_country) ON DUPLICATE KEY UPDATE address_id = LAST_INSERT_ID(address_id);
Edited by jstfsklh211