http://dev.mysql.com/doc/mysql/en/INSERT.html
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL
4.1.0), and a row is inserted that would cause a duplicate value
in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
performed.
Daevid Vincent wrote:
> I'm developing a program where I try an "UPDATE ... LIMIT 1" and if
> mysql_affected_rows == 0, then I know nothing was updated and so I do an
> INSERT. I find this is much cleaner and the majority of the time, I'm going
> to do UPDATES, so I didn't want to waste a SELECT (even though I hear
> they're "cheap"). I'm doing these queries several times per second.
>
> however... Of course UPDATE doesn't 'ERROR" if the record doesn't exist, it
> just didn't do anything (therefore that's why I use the mysql_num_rows() to
> check). The problem is that if I am actually doing an UPDATE to a record
> where nothing actually changed in the existing record, I still get
> mysql_affected_rows() equal to 0. *grrr*.
>
> It would be extremely useful to somehow get a result of maybe -1 if I tried
> to update a record that didn't exist, versus a result of -2 if I tried to
> update a record that did exist, but mySQL didn't change anything.
>
> I don't know exactly what I'm asking for other than a way to know the
> difference...
>
> At the very least, it seems to me that if I update a record that exists
> already (even if no data changed), I should still get mysql_affected_rows()
>
>>0 (since in theory I matched something, even if mySQL behind the scenes
>
> didn't change the data).
>
> Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
> since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
> could try this idea, but I'm home and my code is at work right now and I
> just had the idea! ;-]
>
> http://daevid.com
>
>
