----- Original Message ----- From: "Michael Ragsdale" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 13, 2004 11:10 AM Subject: mysql_insert_id() for UPDATE?
> I've been using mysql_insert_id() with great success, but now I've got a > problem. I'm using UPDATE to, well, update a record in a database and > according to the docs... > > >mysql_insert_id() is updated after INSERT and UPDATE statements that > >generate an AUTO_INCREMENT value or that set a column value to > >LAST_INSERT_ID(expr). > I can't imagine why mysql_insert_id() would be changed by an UPDATE statement. I can't help but wonder if this is simply an error in the documentation. If you look at the article at http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html, it also states that mysql_insert_id() is changed by INSERT and UPDATE. However, when it lists the exact circumstances under which mysql_insert_id() changes, it doesn't list any involving UPDATE. Also, if you look at the article on the UPDATE statement, there is no mention of mysql_insert_id() changing as a result of UPDATE. > If the record did not exist and UPDATE performed as INSERT, then I would > theoretically have no problem. As far as I know, an UPDATE in MySQL *never* does an INSERT, it only changes values on the rows that satisfy the update statement. > Hower, since the record that I'm updating > already exists, the AUTO_INCREMENT value also already exists and that value > is not being re-generated. Therefore, the value that mysql_insert_id() is > returning is zero. According to http://dev.mysql.com/doc/mysql/en/mysql_insert_id.html, "if the previous statement returned an error, the value of mysql_insert_id() is undefined." Is it possible that your statement failed and that is why you are getting zero in mysql_insert_id()? For example, perhaps you tried to set an integer column to a value that was too big for an integer column. > Is there a similar way to capture the id of the record > that is being updated? > How many rows are you changing in your UPDATE statement? Let me take a minute to review some SQL basics with you. I'm not clear how knowledgeable you are so forgive me if you know this already. Your update statement controls which row or rows are being updated. For example: --- update employees set salary = salary * 1.05 where empno = 123 --- The preceding statement gives a 5% salary increase to the person whose employee number is 123. Assuming that empno is a unique (or primary) key, then only that one row got changed. [If empno is NOT a unique or primary key, all rows that had an empno of 123 are updated. I feel strongly that every table should have a primary key although there might, very rarely, be a case that justifies a table with no primary key.] If your updates are based on a primary or unique key, the statement itself tells you exactly which row was updated by the statement. --- update employees set salary = salary * 1.05 where deptno = 'D21' --- The preceding statement gives a 5% raise to every employee in department D21, regardless of how many there are. --- update employees set salary = salary * 1.05 --- The preceding statement gives a 5% raise to every employee that is in the table at the time the update takes place. If you execute the second and third examples, it should be self-evident that potentially thousands or millions of rows will be affected. Would you really expect MySQL to display a list of all the keys of the rows that are being changed? I expect not. Why would you want only part of the list of keys that was updated, either the first or last one? Offhand, I can't think of a good reason for having either one of them. Are you executing this update from a command line or in a script or a program? If you are using a program, what language are you using? Also, what version of MySQL are you running? I'm sorry if my reply is a bit rambly; I'm really just thinking out loud because I'm not very clear on what you are doing. If you could explain a bit more about what you are trying to do, particularly the number of rows you are attempting to update, we might be able to make some useful suggestions. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
