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.
REPLACE INTO
? dev.mysql.com/doc/refman/5.0/en/replace.html – Phil Cross 2 mins ago