Hi all, it is possible in one SQL statement to insert a record then take the autoincrement id, and update for the same record one specific column with this autoincrement value.
Thanks in Advance.
A:
That's not possible at all.
You have to either do this separately or you may create a function/stored procedure to achieve this mission.
enesismail
2010-09-14 11:41:55
A:
Multiple statements can be separated by a semicolon, but I believe you need to use a function in PHP to get the autoincrement value. Your best bet might be to use a stored procedure.
Jay
2010-09-14 11:42:24
mysql_query only executes a single command! You can't separate queries by a semicolon. You can use mysql_insert_id() to get the id of the last insert.
halfdan
2010-09-14 11:43:52
According to http://dev.mysql.com/doc/refman/4.1/en/c-api-multiple-queries.html it is supported in the C api, figured it would be available in PHP also.
Jay
2010-09-14 11:52:06
http://de2.php.net/mysql_query - mysql_query() sends a unique query (multiple queries are not supported)
halfdan
2010-09-14 11:58:22
Not sure where it is stated that OP needs PHP, but on the subject of multiple queries, of course PHP supports multiple queries in single call... see http://ch.php.net/manual/en/mysqli.multi-query.php
Unreason
2010-09-14 12:02:01
@Unreason - the question is tagged PHP.
Jay
2010-09-14 12:09:12
@Unreason it is true that you can send multiple queries, but only if you use the MySQLi Extention, not with the regular MySQL extension, and MySQLi is not available for PHP4
Nicky De Maeyer
2010-09-14 12:29:22
For those not researching this question, here is a link to PHP.net that states that PHP can indeed perform multiple queries in one! http://php.net/manual/en/mysqli.multi-query.php
Michael Eakins
2010-09-14 13:20:56
+1
A:
Doing an insert automatically fills in the value for an auto_increment column (just define it to use AUTO_INCREMENT). There is no need to have the same value twice in one record.
Doing an UPDATE + INSERT together is not possible in a single query.
halfdan
2010-09-14 11:42:43
+1
A:
I found this artcile that may be of interest to you:
http://www.daniweb.com/forums/thread107837.html
They suggest it is possible to do the insert and update in one query.
They show a query like:
INSERT INTO table (FIELD) VALUES (value) ON DUPLICATE KEY UPDATE FIELD=value
I hope this helps and to all the nay sayers, anything is possible.
While I believe it is possible, your safest bet is probably to split this operation up into three stages.
I successfully did this on my own database locally with this code:
INSERT INTO status set status_id = 5 ON DUPLICATE KEY UPDATE status_id=5;select last_insert_id()
You should be able to transform it to work for you.
Michael Eakins
2010-09-14 11:52:06
+3
A:
Strictly speaking you can not do it in a single SQL statement (as others have already pointed out).
However, since you mention that you want to avoid making changes to legacy application let me clarify some options that might work for you.
If you had a trigger on the table that would update the second column, then issuing single insert will give you what you want and you might not need to change anything in the application
If possible, you could rename the table and in its place put a VIEW with the same name. With such simple view it might be transparent to your application (not sure if VIEW would remain updateable with your framework, but generally speaking it should)
Finally, with mysqli library you are free to issue multiple SQL statements, so it will be a single call to the database - which might be enough for you, depending on how exactly you define 'single statement'
None of the above will never be comparable to fixing the application in terms of maintainability for the guy who will inherit your code.
Unreason
2010-09-14 12:27:07
A:
You can write a AFTER INSERT trigger which takes max(id) and updates the record
Anil
2010-09-14 12:28:52