6 messages in com.mysql.lists.mysqlRe: Feature Request: UPDATE 'error co...| From | Sent On | Attachments |
|---|---|---|
| Daevid Vincent | 26 May 2004 22:03 | |
| Robert J Taylor | 26 May 2004 23:15 | |
| Steve Meyers | 27 May 2004 07:41 | |
| Daevid Vincent | 27 May 2004 12:38 | |
| Sasha Pachev | 31 May 2004 13:14 | |
| Robert A. Rosenberg | 31 May 2004 21:14 |
| Subject: | Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate" |
|---|---|
| From: | Robert J Taylor (rob...@rjamestaylor.com) |
| Date: | 05/26/2004 11:15:31 PM |
| List: | com.mysql.lists.mysql |
Does REPLACE INTO not work in your case? | |
|REPLACE| works exactly like |INSERT|, except that if an old record in the table has the same value as a new record for a |PRIMARY KEY| or a |UNIQUE| index, the old record is deleted before the new record is inserted. See section 14.1.4 |INSERT| Syntax <http://dev.mysql.com/doc/mysql/en/INSERT.html>.
http://dev.mysql.com/doc/mysql/en/REPLACE.html
HTH,
Robert J Taylor rob...@rjamestaylor.com
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! ;-]