List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 30 2007 8:12pm
Subject:RE: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...
View as plain text  
> -----Original Message-----
> From: Philip Hallstrom [mailto:mysql@stripped] 
>
> Because if you wanted that you'd use REPLACE which is mysql 
> specific which is okay since it's mysql you're using I guess.

Except for the CRITICAL issue that REPLACE will DELETE the row first,
thereby causing all foreign keys to be deleted that are set to be 'ON DELETE
CASCADE'.

> And in my case, I have a stats table... I either want to 
> insert a row with 
> hits=1 or I want to hits=hits+1.  So I need to specify what I 
> want.  And I 
> don't want to change *any* of the other columns (such as the 
> date for the hit or the id, etc.)

Perhaps I'm wrong here, but my suggestion should still work wouldn't it?

"insert into table1 (hits) values (hits+1) on duplicate key update;"

So on a new record, all columns inserted as defaults.
hits is 0, then incremented to 1.

On an updated record, hits is just incremented.
No other columns are touched since they weren't called out explicitly.

Thread
UPDATE and INSERT in onelists20 Apr
  • Re: UPDATE and INSERT in oneJohan Höök20 Apr
    • INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...Daevid Vincent28 Apr
      • Re: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet sofar...Philip Hallstrom28 Apr
        • RE: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...Daevid Vincent30 Apr
  • Re: UPDATE and INSERT in oneDavid Precious20 Apr
Re: UPDATE and INSERT in onewang shuming20 Apr