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:[EMAIL PROTECTED] > Sent: Thursday, May 27, 2004 7:42 AM > To: Daevid Vincent > Cc: [EMAIL PROTECTED] > 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 > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
