[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...
From:       "Daevid Vincent" <daevid () daevid ! com>
Date:       2007-04-30 20:12:29
Message-ID: BRAKAd5X00lfepHjon600000c76 () mx1 ! lockdownnetworks ! com
[Download RAW message or body]

> -----Original Message-----
> From: Philip Hallstrom [mailto:mysql@philip.pjkh.com] 
>
> 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.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]