Using Update Query to Copy Column Data

I need to copy the value in a column named TEAM from one row into another row. Both rows need to have the same team name. This is my query that doesn't work:

$query = "UPDATE profiles SET team = (SELECT team FROM profiles WHERE id = '$coach_id') WHERE id = '$player_id'";

I have tried removing single quotes, removing "FROM profiles", changing value to table.value, tried to give a newdata.clan alias, and I have even tried changing the values to integers instead of parameters. Nothing works, and this is what I get:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = '') WHERE id = ''' at line 3

开发者_Go百科

$query1 = "SELECT team FROM profiles WHERE id = '$coach_id'";

/* get the value of the first query and assign it to a variable like $team_name */

$query2 = "UPDATE profiles SET team = '$team_name' WHERE id = '$player_id'";

Also, you should surround your PHP variables in curly braces:

$query = "UPDATE profiles SET team = \"(SELECT team FROM profiles WHERE id = '{$coach_id}')\" WHERE id = '{$player_id}'";

From the MySQL manual:

"Currently, you cannot update a table and select from the same table in a subquery."

Source: http://dev.mysql.com/doc/refman/5.0/en/update.html

Use the method that FinalForm wrote:

<?
$coach_id = 2;
$player_id = 1;

$query1 = "SELECT team FROM profiles WHERE id = '$coach_id'";
$rs = mysql_query($query1);
if ($row = mysql_fetch_array($rs)) {
  $team_name = $row['team'];
  $query2 = "UPDATE profiles SET team = '$team_name' WHERE id = '$player_id'";
  mysql_query($query2);
  // Done, updated if there is an id = 1
} else {
  // No id with id = 2
}
?>