使用更新查询复制列数据

发布于 2024-11-16 21:18:23 字数 440 浏览 0 评论 0原文

我需要将名为 TEAM 的列中的值从一行复制到另一行。两行需要具有相同的团队名称。这是我的查询不起作用：

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

我尝试删除单引号，删除“FROM profile”，将值更改为 table.value，尝试提供 newdata.clan 别名，我什至尝试将值更改为整数参数。什么都不起作用，这就是我得到的：

错误：您的 SQL 中有错误句法;检查手册对应你的MySQL服务器正确使用语法的版本靠近 'WHERE id = '') WHERE id = ''' at 第 3 行

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

面犯桃花 2024-11-23 21:18:23

$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'";

$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'";

回复收藏 0 原文

情绪失控 2024-11-23 21:18:23

另外，您应该将 PHP 变量括在大括号中：

$query = "UPDATE profiles SET team = \"(SELECT team FROM profiles WHERE id = '{$coach_id}')\" 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}'";

回复收藏 0 原文

征棹 2024-11-23 21:18:23

来自 MySQL 手册：

“目前，您无法更新表
并从同一个表中选择
子查询。”

来源：http://dev.mysql.com/doc/ refman/5.0/en/update.html

使用FinalForm写的方法：

<?
$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
}
?>

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
}
?>

回复收藏 0 原文

~没有更多了~