I am inserting a single row into a table, one of the columns though needs the lastInsertID to append another column. We are building file names off of record IDs. Instead of doing a 2nd
update to the newly inserted record, is it possible to get the value of the ID column and append to it in the same mysql insert?
Using PHP, currently I am doing:
$query = "INSERT INTO table (id,idName) values (null,?)" ; list($lastID,$inError,$inResult) = dbInsert($query,array("")) ; Then: $appString = $lastID . "-S1001.pdf" ; $query = "UPDATE table SET idName=? WHERE id=?" ; list($upCount,$upError) = dbUpdate($query,array($appString,$lastID)) ;
But is there a way to merge the INSERT and UPDATE into a single statment?
$apString = "-S1001.pdf" ; $query = "INSERT INTO table (id,idName) values (null,id.$appString)" ; list($lastID,$inError,$inResult) = dbInsert($query,array()) ;
Why not create a
trigger that will do your update whenever you insert a row into the table?
This way, you don’t need to manually do the second query. Just the insert.