replace into

Posted by admin on Apr 10, 2012 in Database |

I had been using mysql’s replace into syntax until I read this article http://code.openark.org/blog/mysql/replace-into-think-twice which makes a lot of sense.

I have mostly changed statements to use insert .. on duplicate key instead.
For example


    insert into currentusage (
      userid, 
      anytimebytesuploaded, 
      anytimebytesdownloaded, 
      peakbytesuploaded, 
      peakbytesdownloaded, 
      offpeakbytesuploaded,
      offpeakbytesdownloaded,
      bytesexcluded)
    select
      userid,
      sum(case when timing = 'ANYTIME' then bytesuploaded end),
      sum(case when timing = 'ANYTIME' then bytesdownloaded end),
      sum(case when timing = 'PEAK'    then bytesuploaded end),
      sum(case when timing = 'PEAK'    then bytesdownloaded end),
      sum(case when timing = 'OFFPEAK' then bytesuploaded end),
      sum(case when timing = 'OFFPEAK' then bytesdownloaded end),
      sum(bytesexcluded)
    from netflowsummary
      where userid = 12350
      and summaryinterval >= '2012-04-04'
    on duplicate key update
      anytimebytesuploaded=values(anytimebytesuploaded),
      anytimebytesdownloaded=values(anytimebytesdownloaded),
      peakbytesuploaded=values(peakbytesuploaded),
      peakbytesdownloaded=values(peakbytesdownloaded),
      offpeakbytesuploaded=values(offpeakbytesuploaded),
      offpeakbytesdownloaded=values(offpeakbytesdownloaded),
      bytesexcluded=values(bytesexcluded)

Leave a Reply

XHTML: You can use these tags:' <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Copyright © 2008-2017 Brinsmead Data Services All rights reserved.