Jeremy Jeremy - 2 years ago 78
SQL Question

Mysql UPDATE ON DUPLICATE KEY only inserting

I am trying to run a query so that it will insert or update on duplicate. I am using a unique index for the duplicate but I cannot seem to get it to work. It still adds new records. Hoping some fresh eyes can point out my issue. Thanks.

This is my schema

CREATE TABLE IF NOT EXISTS `pricing_puchasing` (
`custno` varchar(6) DEFAULT NULL COMMENT 'customer code',
`recipe` varchar(15) DEFAULT NULL,
`item` varchar(120) NOT NULL COMMENT 'Item Code from dProduce',
`unit_weight` double(12,4) DEFAULT NULL,
`case_cost` double(12,4) DEFAULT NULL COMMENT 'Projected cost of item',
`cost_per_lb` double(12,4) NOT NULL,
`projected_price` float(12,3) DEFAULT NULL COMMENT 'projected Price',
`projected_margin` float(12,3) DEFAULT NULL COMMENT 'Projected Margin',
`trend` tinyint(1) DEFAULT NULL COMMENT 'Trend status 1=up, 0=down',
`note` varchar(255) DEFAULT NULL COMMENT 'Note about the data',
`week_of` date NOT NULL COMMENT 'Week of this data',
`approved` tinyint(1) DEFAULT NULL COMMENT 'approval flag',
`last_edited_from` varchar(20) NOT NULL COMMENT 'identifier to know where it was last saved from',
`saved_at` datetime NOT NULL COMMENT 'Date time of save/update',
`saved_by` int(11) NOT NULL COMMENT 'Created by user ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores pricing purchasing data';

ALTER TABLE `pricing_puchasing`
ADD UNIQUE KEY `custno_recipe_item_week_of` (`custno`,`recipe`,`item`,`week_of`), ADD KEY `custno` (`custno`);

This is the query I am testing:

INSERT INTO pricing_puchasing

I fixed the issue with custno missing but I still am having issue with it inserting duplicates. I created a sqlfiddle. This time I am testing with 2 NULL values and still a no go... Is it my schema or is this still an issue with my query?

Answer Source

A NULL value is not consider to be "unique". The first column in the unique index is custno, you aren't providing a value for that column in the INSERT, so the default value of NULL is used as the value for that column.

Because of the NULL value is not considered to be unique, the INSERT will not throw a duplicate key exception.

Try it with a non-NULL value for the custno column.

Also, you can use the special VALUES() function in the UPDATE portion of the statement, to reference the value that was supplied in the INSERT.


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download