3 in 1 mysql statement

it is possible in one SQL statement to insert a record then take th开发者_如何学Goe autoincrement id, and update for the same record one specific column with this autoincrement value.

Thanks in Advance.

---

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.

---

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.

---

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.

---

You can write a AFTER INSERT trigger which takes max(id) and updates the record

---

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.

---

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.