如何复制mySQL表行但插入一个自定义值？

Question

With this code I am duplicating my table row:

 $old_name = "Laura";
 $new_name = "Sam";

 $pdo = $db->prepare('INSERT INTO animals (status, name) SELECT status, name FROM animals WHERE name = :name');
 $pdo->execute(array(
        ':name' => $old_name,
  )); 

It is working well, but I need to put on the position of name by the new insert a custom value.

This is my approach:

 $old_name = "Laura";
 $new_name = "Sam";

 $pdo = $db->prepare('INSERT INTO animals (status, name) values(:name) SELECT status, name FROM animals WHERE name = :name');
 $pdo->execute(array(
        ':name' => $new_name,
        ':name' => $old_name,
  )); 

The error message is:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Answer 1

You don't need VALUES() with INSERT SELECT and there's probably some ambiguity on the column names since you're both selecting from, and inserting to, the same table - so you should alias one of them.

Also, since you're entering different values on the :name placeholder, you're going to need two different placeholders.

This should probably do it:

$old_name = "Laura";
$new_name = "Sam";

$pdo = $db->prepare('INSERT INTO animals(animals.status, animals.name) SELECT a.status, :new_name FROM animals AS a WHERE a.name = :old_name');
$pdo->execute(array(
    ':old_name' => $old_name,
    ':new_name' => $new_name,
));