INSERT INTO and ON DUPLICATE KEY

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jtw
    #1

    INSERT INTO and ON DUPLICATE KEY

    I need to update a statistics table through out the day. I would like
    to insert the first data sampling of the data, then update the
    existing record for the rest of the day. The problem is that I do not
    alway know when the first sampling will start.

    Keys for the table are username and date. So each user will have only
    one entry per day.

    I have been checking to see if the record exists - select .. where
    username=... AND date =.... Seems like there is a better way.


    I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
    this to work with PHP.


    Sample would be.

    INSERT INTO usagestats (username, date, usageinfo)
    VALUES(username = 'jon', date = '2003-09-19', useageinfo='250 315')
    ON DUPLICATE KEY UPDATE usageinfo = '250315'


    In this case if the record with username='jon' and date='2003-09-19'
    already exists, then the ON DUPLICATE KEY would update this record
    with usageinfo = '...'


    What am I missing?

    jtw



    --

    http://www.UsenetRocket.com

    Tags: None
    • Andy Hassall
      #2
      Re: INSERT INTO and ON DUPLICATE KEY

      On Fri, 19 Sep 2003 16:46:58 -0400, jtw <jwright@mailbo xspot.com> wrote:
      [color=blue]
      >I need to update a statistics table through out the day. I would like
      >to insert the first data sampling of the data, then update the
      >existing record for the rest of the day. The problem is that I do not
      >alway know when the first sampling will start.
      >
      >Keys for the table are username and date. So each user will have only
      >one entry per day.
      >
      >I have been checking to see if the record exists - select .. where
      >username=... AND date =.... Seems like there is a better way.[/color]

      Depending on whether inserts or updates are more likely:

      INSERT the row - catch the key violation (1036) error - if you got one, do an
      UPDATE instead.

      or

      UPDATE the row - check mysql_affected_ rows - if zero, do an INSERT.
      [color=blue]
      >I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
      >this to work with PHP.
      >
      >Sample would be.
      >
      >INSERT INTO usagestats (username, date, usageinfo)
      > VALUES(username = 'jon', date = '2003-09-19', useageinfo='250 315')
      > ON DUPLICATE KEY UPDATE usageinfo = '250315'
      >
      >In this case if the record with username='jon' and date='2003-09-19'
      >already exists, then the ON DUPLICATE KEY would update this record
      >with usageinfo = '...'
      >
      >What am I missing?[/color]

      The error message or unexpected behaviour you're seeing, and your MySQL
      version.

      The manual says that syntax isn't available until MySQL 4.1.

      --
      Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
      Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

        Comment

        • jtw
          #3
          Re: INSERT INTO and ON DUPLICATE KEY

          Thanks Andy. Sometimes the simple solutions are the best.

          jtw



          On Fri, 19 Sep 2003 22:04:30 +0100, Andy Hassall <andy@andyh.co. uk>
          wrote:
          [color=blue]
          >On Fri, 19 Sep 2003 16:46:58 -0400, jtw <jwright@mailbo xspot.com> wrote:
          >[color=green]
          >>I need to update a statistics table through out the day. I would like
          >>to insert the first data sampling of the data, then update the
          >>existing record for the rest of the day. The problem is that I do not
          >>alway know when the first sampling will start.
          >>
          >>Keys for the table are username and date. So each user will have only
          >>one entry per day.
          >>
          >>I have been checking to see if the record exists - select .. where
          >>username=.. . AND date =.... Seems like there is a better way.[/color]
          >
          > Depending on whether inserts or updates are more likely:
          >
          >INSERT the row - catch the key violation (1036) error - if you got one, do an
          >UPDATE instead.
          >
          >or
          >
          >UPDATE the row - check mysql_affected_ rows - if zero, do an INSERT.
          >[color=green]
          >>I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
          >>this to work with PHP.
          >>
          >>Sample would be.
          >>
          >>INSERT INTO usagestats (username, date, usageinfo)
          >> VALUES(username = 'jon', date = '2003-09-19', useageinfo='250 315')
          >> ON DUPLICATE KEY UPDATE usageinfo = '250315'
          >>
          >>In this case if the record with username='jon' and date='2003-09-19'
          >>already exists, then the ON DUPLICATE KEY would update this record
          >>with usageinfo = '...'
          >>
          >>What am I missing?[/color]
          >
          > The error message or unexpected behaviour you're seeing, and your MySQL
          >version.
          >
          > The manual says that syntax isn't available until MySQL 4.1.
          >[/color]

          --

          http://www.UsenetRocket.com

            Comment

            Previous template Next
            Working...