List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 27 2004 7:38pm
Subject:RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"
View as plain text  
Well, my "hack" (which is sort of like what you suggest) is to change my
primary key from just an auto_increment 'id' field to a combination of two
other fields (mac/scanner_id) that I know must be unique. Then I rely upon
the fact that mySQL will not allow a duplicate PK. (I did say it was a
hack). A co-worker assures me that a SELECT is cheap, however a version I
tried (without my hack) still allowed duplicates to slip through because I
wasn't locking the tables. I have multiple scanners hitting the same table
and locking seems to me a bad idea.

Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
field is 1 second and these queries happen faster than that. *Neuman!*  :-/

REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.

http://daevid.com

> -----Original Message-----
> From: Steve Meyers [mailto:steve-mysql@stripped] 
> Sent: Thursday, May 27, 2004 7:42 AM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: Feature Request: UPDATE 'error codes' or 
> mysql_affected_rows() to be more "accurate"
> 
> 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
> > 
> > 
> 

Thread
Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"Daevid Vincent27 May
  • Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows()to be more "accurate"Robert J Taylor27 May
  • Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows()to be more "accurate"Steve Meyers27 May
    • RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"Daevid Vincent27 May
      • Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows()to be more "accurate"Sasha Pachev31 May
      • RE: Feature Request: UPDATE 'error codes' ormysql_affected_rows() to be more "accurate"Robert A. Rosenberg1 Jun