2

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

...
  • 1
    This is sometimes referred to as an "Upsert": to UPdate or inSERT. – Kevin Hogg Mar 7 '14 at 13:24
5

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) ;
  • Exactly what I was searching for. +1 – CappY Mar 20 '15 at 8:48
1
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`
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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