There is always the possibility of using a trigger on INSERT - would that solve your
problem? I think that prior to MySQL 5.1 you need SUPER privileges to work with triggers,
after that it's a separate permission. I see no reason why an INSERT trigger wouldn't pick
up the correct value from the autoincremented column. Alternatively, you could put the
trigger on UPDATE (on UPDATE SET b=autoidfield) and then simply run an UPDATE tbl SET
autoidfield=autoidfield after your INSERT - that should still throw the trigger, and
because the row's already been inserted, the autoincremented value will be set and
Otherwise, what isn't working when you SELECT LAST_INSERT_ID() via MyODBC? I code a global
function into all my VBA/MyODBC projects that takes a ADODB connection and returns the
last insert id just by running that select. As long as you maintain the same connection
object for the INSERT and the subsequent SELECT (or in your case UPDATE) then
last_insert_id should be retrieved correctly.
If not, could you post back what you do get?
Hope that helps,
From: Jerry Schwartz [mailto:jerry@stripped]
Sent: 21 January 2011 19:57
Subject: Retrieving last_insert_id
I have an auto_increment field in my table, but I need to set another field to the same
value. (Please don’t ask why.)
It seems I would need to insert the record, and then update it; but I haven’t
figured out how to get the value of last_insert_id().
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: <mailto:jerry@stripped> jerry@stripped
Web site: <http://www.the-infoshop.com/> www.the-infoshop.com