# Insert into … (…) (Select…) on dupliacate key update

Can you help me to solve a problem. The task is to insert a record in MySQL database. terms: If the record exists - to update the record.
If the record does not exist - to create a new one. The Key columns in the table 'NETFLOW'.'Month' are 'Year','Month','Day','Hour','srcaddr','dstaddr'.

The thing is to do request as quickly as possible and if possible in one request.

The text of the current request is:

``````    INSERT INTO `netflow`.`Month` (`Year`, `Month`, `Day`, `Hour`, `srcaddr`,`dstaddr`, `doctets`)
(
SELECT
YEAR( FROM_UNIXTIME(  `unix_secs` ) ) AS `YEAR`,
MONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `MONTH`,
DAYOFMONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `DAY`,
HOUR( FROM_UNIXTIME(  `unix_secs` ) ) AS `HOUR`,
`doctets`
FROM  `netflow`.`raw`
WHERE `unix_secs` <= 1394179200
GROUP BY
`YEAR`,
`MONTH`,
`DAY`,
`HOUR`,
)
ON DUPLICATE KEY UPDATE

...
``````
-
This is sometimes referred to as an "Upsert": to UPdate or inSERT. –  Kevin Hogg Mar 7 at 13:24

if you want to change the value with the new one, you can use:

``````ON DUPLICATE KEY UPDATE
doctets = VALUES(doctets) ;
``````

or if you want to add the new value to the existing one:

``````ON DUPLICATE KEY UPDATE
doctets = doctets + VALUES(doctets) ;
``````
-
``````INSERT INTO `netflow`.`Month` (`Year`, `Month`, `Day`, `Hour`, `srcaddr`,`dstaddr`, `doctets`)
(
SELECT
YEAR( FROM_UNIXTIME(  `unix_secs` ) ) AS `YEAR`,
MONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `MONTH`,
DAYOFMONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `DAY`,
HOUR( FROM_UNIXTIME(  `unix_secs` ) ) AS `HOUR`,
`doctets`
FROM  `netflow`.`raw`
WHERE `unix_secs` <= 1394179200
GROUP BY
`YEAR`,
`MONTH`,
`DAY`,
`HOUR`,
)
ON DUPLICATE KEY UPDATE SET col_name1 = val1, col_name2 = val2
``````

you can also use `REPLACE INTO`. If the key already exists then it will update the record else it will insert a new record.

``````REPLACE INTO `netflow`.`Month` (`Year`, `Month`, `Day`, `Hour`, `srcaddr`,`dstaddr`, `doctets`)
(
SELECT
YEAR( FROM_UNIXTIME(  `unix_secs` ) ) AS `YEAR`,
MONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `MONTH`,
DAYOFMONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `DAY`,
HOUR( FROM_UNIXTIME(  `unix_secs` ) ) AS `HOUR`,