How to insert the value derived from AUTO_INCREMENT into another column in the same INSERT query?

Question

I have an id column which is a primary key with AUTO_INCREMENT. I need the value that is generated to be inserted into the id column, as well as another column (which isn't set to AUTO_INCREMENT, and isnt unique.

Currently I use the mysqld_isnert_id() function to get the id, and simply run an update query after the insert, but I was wondering if I could do this without running the 2nd update query.

Answer 1

If I recall correctly, the automatically generated ID isn't even created until after the insert has been performed. Your two query way is probably the only way without diving into perhaps a stored procedure.

Answer 2

after insert Trigger?

Answer 3

You could define a trigger along the lines of:

delimiter //
CREATE TRIGGER upd_check AFTER INSERT ON mainTable
FOR EACH ROW
BEGIN
  UPDATE dependingTable 
  SET dependingTable.column = NEW.id
END;//
delimiter ;

I am not exactly sure WHEN the AUTO_INCREMENT value is generated, but you could try the following, since if it works it'll save you an update (If the column you want the value replicated to is in the same row as the inserted row):

CREATE TRIGGER upd_check BEFORE INSERT ON mainTable
FOR EACH ROW 
SET NEW.column = NEW.id

Answer 4

The only way I can see you doing it with a single query is to use the information schema. In the information schema there is a table called 'tables', there you access the column auto_increment. That contains the NEXT insert id for that table, you can access this via a nested select, just give the user used to connect to the database read access to that table. This will only work with innodb engines as far as I can tell as that way the nested select you'll do to populate the second id field will be part of the greater transaction of the insert.

That's what your query might look like:

INSERT INTO fooTable VALUES (0, (SELECT AUTO_INCREMENT FROM information_schema.TABLES));

Also if you're worried about read access and security issues, just remember this is the same info you can get by running a show table status. Speaking of which, I tried to see if you could project the show commands/queries via a select and you can't, which totally sucks, because that would have been a much cleaner solution.