使用MySQL:使用内部联接使用值更新字段 [英] Using MySQL: Update field with values using Inner Join

问题描述

我有一个主表和一个索引表.这两个表共享一个公用的主字段，称为"L_Status".我想基于称为状态"的索引表中的引用，将主表中的数据从"L_Status"值(整数)更新为"L_StatusLV"(可读文本值).这是我输入到PHPmyAdmin中以完成此操作的代码:

I have a main table and an index table. Both tables share a common primary field called "L_Status". I want to update the data in the main table from "L_Status" values (integers) into "L_StatusLV" (readable text values) based on the reference in the index table called "status". Here is the code I've entered into PHPmyAdmin to accomplish this:

UPDATE markers.L_Status 
FROM markers
INNER JOIN STATUS ON markers.L_Status = status.L_Status
WHERE  markers.L_Status = status.L_StatusLV

PHPmyAdmin返回以下错误:

PHPmyAdmin returns the following error:

1064-您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在"FROM标记INNER JOIN STATUS ON标记附近使用.L_Status= status.L_Status WHERE ma"位于第2行

1064 - 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 'FROM markers INNER JOIN STATUS ON markers.L_Status = status.L_Status WHERE ma' at line 2

这里有关于语法错误的任何建议吗?

Any advice on the sytax error here?

推荐答案

MySQL的UPDATE JOIN语法与SQL Server的语法(看起来像您所拥有的)不同:

MySQL's UPDATE JOIN syntax differs from SQL Server's (which looks like what you have):

UPDATE
  /* First join the tables */
  markers 
  INNER JOIN status ON markers.L_Status = status.L_Status
/* Then specify the new value in the SET clause */
SET markers.L_Status = status.L_StatusLV

但是，如上所述，当前值是整数.如果列markers.L_Status是INT列，而不是我认为人类可以理解的列，则不是CHAR/VARCHAR，则此列将无效.

However, as you noted above the current values are integers. If the column markers.L_Status is an INT column rather than a CHAR/VARCHAR as I assume the human-readable column is, this won't work.

完整访问 MySQL的UPDATE语法参考语法细节.特别是 table_references .

Visit MySQL's UPDATE syntax reference for the full syntactic details. In particular, the table_references.

这篇关于使用MySQL:使用内部联接使用值更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！