Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

...
share|improve this question
    
This is sometimes referred to as an "Upsert": to UPdate or inSERT. –  Kevin Hogg Mar 7 at 13:24

2 Answers 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`
)
share|improve this answer

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) ;
share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.