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
> >
> >
>
