设置变量等于MySQL中的列 [英] Setting variable equal to column in MySQL

问题描述

我正在尝试将Player_Season列设置为等于表的Year列.我知道如何在没有变量的情况下执行此操作，但是我不太了解MySQL中的变量，并且想知道如何出于教育目的执行此操作.当我只输入一个数字代替Year时，它会影响行.但是，当我输入列名时，错误显示为Unknown column 'Player_Season' in 'field list

I'm trying to set the Player_Season column equal to the Year column of my table. I know how to do this without variables, but I'm not well acquainted with variables in MySQL and would like to know how to do this for educational purposes. It is affecting the rows when I simply enter a number in place of Year. When I enter a column name though, the error reads Unknown column 'Player_Season' in 'field list

SET @var_name = `Year`;
UPDATE retrosheet.batting_table SET Player_Season = @var_name WHERE id IS NOT NULL;

推荐答案

这是您可以在SQL Server中执行的操作...对吗?

This is something you can do in SQL server...right?

不，您不能在任何品牌的SQL数据库中使用变量或预准备的语句参数作为列名.

No, you can't use a variable or a prepared statement parameter as a column name in any brand of SQL database.

您可以内插列名称为字符串，并从中准备语句.这是在MySQL中可用的示例:

You can interpolate the column name into a string and prepare a statement from that. Here's an example that works in MySQL:

SET @var_name = 'Year';
SET @myquery = CONCAT('UPDATE retrosheet.batting_table SET Player_Season = ',
  @var_name, ' WHERE id IS NOT NULL';

PREPARE stmt FROM @myquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

重要的是，所有表和列都必须在准备时固定.

只能在要在查询中放置常量值的地方使用变量和参数，例如带引号的字符串文字，带引号的日期文字或数字文字.

You can use variables and parameters only where you would put constant values in the query, like quoted string literals, quoted date literals, or numeric literals.

这篇关于设置变量等于MySQL中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！