There is a database where id AUTOINCREMENT. How to make a query in such a way, what would this id, which was, autoincrement pulled out and inserted into another field in the same table? The picture is more understandable. Id field and weight

alt text

    4 answers 4

    UPDATE tab SET weight = id WHERE weight= 0
    • hard ... do you propose to clone the entire column to change a single value? what is there about the cons said? - FLK
    • added. we set default for weight 0, update where it is not filled. Alternatively, you can hang a trigger on "after insertion", work with it. - Yura Ivanov

    mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for the future, call mysql_insert_id () immediately after the query that generates this value.

       INSERT INTO table VALUE (NULL, 0, 0); UPDATE table SET weight = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();

        Do you mean that when adding a new record, immediately register the ID of this record in some field? If so, you can get this ID before requesting an INSERT.

         $res_id = mysql_query("SHOW TABLE STATUS LIKE 'table_name';"); $row_id = mysql_fetch_assoc($res_id); $id = $row_id['Auto_increment'];
        • one
          This is a very unreliable method, the "id" needs to be recognized only after insertion. - razielsd
        • "unreliable way"? There will be about 0.0001 seconds between this request and the INSERT request. And now think about the probability with which it is possible that someone will make another INSERT during this period of time - Deonis
        • > it will take about 0.0001 seconds. And now think about the probability of paraphrasing a famous sketch about architects: "... users, as a rule, are calm people, I think the weather will not let them down." - karmadro4
        • I do not like and will not argue, although there would be an opportunity, I would spend with you a simple experiment, as with a “not calm user”. Well, yes, the thing is ... I showed the principle, but if it comes to that, then for the "not calm" nothing prevents us from doing an atomic operation: INSERT INTO table_name ( weight ) SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' and TABLE_NAME = 'table_name' If you are a little more familiar with this than with the work of architects, then there should be no questions)) - Deonis
        • I'm not sure that such an operation will provide parallelism, comparable to the ability to generate sequences provided by the server for client sessions. But much better than a sentence in response. - karmadro4