Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I want to insert new rows in MySQL table only if the combination of 3 of the existing columns (ID, Type, Year) is not unique. In case these 3 columns match I want to update the field 'Val' only.

This is the table:

 CREATE TABLE IF NOT EXISTS `Abs` (
 `ID` bigint(10) NOT NULL,
 `Type` int(11) NOT NULL,
 `Year` int(11) NOT NULL,
 `val` decimal(4,1) DEFAULT NULL, ....

That's why I created composite index and use INSERT ON DUPLICATE KEY, trying to save IF THEN blocks.

In my table there are not any rows with Type = 0, but following seems to insert nothing.

INSERT INTO `Abs` (`ID`, `Type`, `Year`, 'Val') VALUES (141, 0, 2013, 5) 
ON DUPLICATE KEY UPDATE `Val` = 5

This is my composite index: ALTER TABLE Abs ADD PRIMARY KEY( ID,
Type, Year);

What would be the best approach in that case. I assume that this is a pretty common case in RDB world, but couldn't find easy solution.

share|improve this question
Have you looked at REPLACE INTO? dev.mysql.com/doc/refman/5.0/en/replace.html – Phil Cross 2 mins ago

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.