Search code examples
mysqlselectinsertduplicatesmysql-insert-id

MYSQL insert an an new duplicate entry but with a few different defined fields

Presently I am inserting a new entry (copying the data from an existing entry) & then updating this new entry. eg:

$age = 30;

INSERT INTO table (category,sex,age) SELECT category,sex,age FROM table WHERE id = $id
<br>$new_id = mysql_insert_id();
<br>UPDATE sample_table SET age = $age WHERE id = $new_id

However, I would like to save a database interaction by inserting the new entry in one go (without having to get mysql_insert_id / updating the new entry). eg:

$age = 30;

INSERT INTO table (category,sex,$age) SELECT category,sex FROM table WHERE id = $id

Is there a way to insert the above by explicitly defining the age field, or is there another MYSQL command I should be using?

Solution

  • You are looking for this:

    INSERT INTO table (category, sex, age)
    SELECT category, sex, $age
    FROM table
    WHERE id = $id ;

    You can put constants in the select list.