MySQL 更新和加入

Question

我有一个包含联系人详细信息的MySQL表，我需要标记列"PHONE1"的重复项到另一个名为"DUPLICATE"(忽略第一行)

我找到了一个SQL教程，其中有一个SQL查询示例，几乎完全符合我的要求:https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/.

我修改了教程示例，我可以"SELECT"使用以下查询正确地复制行:

SELECT t1.ID, t1.`Contact Name`, t1.PHONE1
FROM new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)

这个"选择";查询工作100%，但当我试图改变这个查询，以实际更新行(按教程)，我得到一个SQL语法错误。下面是生成错误的查询:

UPDATE t1
SET t1.duplicate = t2.ID
FROM new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)

这会导致SQL错误:

#1064 -你有一个错误的SQL语法;检查与MySQL服务器版本对应的手册，以获得正确的语法FROM new_leads_test2 AS t1
INNER JOINt2
ON t1。PHONE1 = t2。第3行

有谁能帮我一下吗?

Answer 1

我们可以把SET放在INNER JOIN之后，因为UPDATE语句，table_reference在SET之前使用

顺便说一句，也许你不需要FROM

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

所以我们可以在下面使用like。

UPDATE new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)
SET t1.duplicate = t2.ID

Answer 2

您的示例是SQL Server语法，而不是MySQL。

试一试:

UPDATE new_leads_test2 t1
INNER JOIN new_leads_test2 t2
ON (t1.PHONE1 = t2.PHONE1 AND t1.ID > t2.ID)
SET t1.duplicate = t2.ID