mysql - 尝试在 mysql 中运行更新查询时出现语法错误

Question

在尝试执行下面给出的查询时，出现语法错误我需要更新表 civicrm_address 中的列值并将其从 abc_abc_drupal_civi_4_17 数据库移动到 abc_drupal 数据库

为了实现它，我在 FROM 附近遇到了语法错误，我得到的错误如下

#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 abc_abc_drupal_civi_4_17.civicrm_address,abc_drupal.civic' at line 3

如何修复它？

UPDATE abc_drupal.civicrm_address 
SET abc_drupal.civicrm_address.state_province_id = abc_abc_drupal_civi_4_17.civicrm_address.state_province_id
FROM abc_abc_drupal_civi_4_17.civicrm_address,abc_drupal.civicrm_address
WHERE abc_drupal.civicrm_address.state_province_id IS NULL 
AND   abc_abc_drupal_civi_4_17.civicrm_address.state_province_id IS NOT NULL
AND   abc_abc_drupal_civi_4_17.civicrm_address.id = abc_drupal.civicrm_address.id  
AND   abc_abc_drupal_civi_4_17.civicrm_address.contact_id IS NOT NULL;

Answer 1

MySQL 将 JOIN 放在 UPDATE 子句中，而不是通过单独的 FROM (FROM 用于SQL Server 和 Postgres)。

您所写的查询很难破译。我强烈建议您使用表别名，这样查询就更容易编写和阅读:

UPDATE abc_drupal.civicrm_address a JOIN
       abc_abc_drupal_civi_4_17.civicrm_address aa
       ON aa.id = a.id
    SET a.state_province_id = aa.state_province_id
WHERE a.state_province_id IS NULL 
      aa.state_province_id IS NOT NULL AND   
      aa.contact_id IS NOT NULL;