Insert into ... (...) (Select...) on duplicate key update

Question

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 query as quickly as possible and if possible in one query.

The text of the current query 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`,  
        `srcaddr` ,  
        `dstaddr` ,  
        `doctets` 
      FROM  `netflow`.`raw` 
        WHERE `unix_secs` <= 1394179200
      GROUP BY 
        `YEAR`, 
        `MONTH`, 
        `DAY`, 
        `HOUR`,
        `srcaddr`,
        `dstaddr`
    )
    ON DUPLICATE KEY UPDATE

...

Answer 1

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) ;

Answer 2

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`,  
    `srcaddr` ,  
    `dstaddr` ,  
    `doctets` 
  FROM  `netflow`.`raw` 
    WHERE `unix_secs` <= 1394179200
  GROUP BY 
    `YEAR`, 
    `MONTH`, 
    `DAY`, 
    `HOUR`,
    `srcaddr`,
    `dstaddr`
)
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`,  
    `srcaddr` ,  
    `dstaddr` ,  
    `doctets` 
  FROM  `netflow`.`raw` 
    WHERE `unix_secs` <= 1394179200
  GROUP BY 
    `YEAR`, 
    `MONTH`, 
    `DAY`, 
    `HOUR`,
    `srcaddr`,
    `dstaddr`
)