Mysql writing id in other fields

[mcibor]

Hi All!

I have a table: id, name, id2

I would like to create a new record with values:

[new id (auto_increment), $name, the same id as the record's]

For now I do this in 3 querries:
1. INSERT INTO table(name) VALUES('$name');
2. $id = SELECT LAST_INSERT_ID(); (with normal mysql_querry, etc, not just like this)
3. UPDATE table SET id2='$id' WHERE id='$id';

How to do that all in one querry?

Thanks for any answers!
Michal Cibor

[rojer_31]

Hi,
What are you really trying to do here? Do you really want redundant data in your database?
It could be done in 2 queries, but I wouldn't know how to do it in 1 :(
Just select the max value of the id field in the first query(the auto-incrementing id), then insert the data with id2 set to max+1.

[mcibor]

Yes, I need that redundant data, because it allows me to undo the whole operation (even after some time). Thank you for the code on how to simplify the script, however I have some doubt about using manual incrementation: eg what will happen if in the meantime someone creates a new record?

I think I'll stay with the original code, although it's not perfect. I have a bit of processing going on between 1. and 3 query.

Thanks again for the answer!
Best regards
Michal Cibor

BTW Welcome to WebMasterWorld!

[coopster]

Some windbag left a lengthy explanation in a similar post a few months back regarding primary key and another field the same.... how to? [webmasterworld.com] Not a bad read though ;)