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

lp0sw83n 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(281)

在尝试执行下面给出的查询时，我得到一个语法错误，我需要从表civicrm\u address更新列值，并将其从\u \u drupal\u civi\u 4\u 17数据库移动到\u drupal数据库
为了实现它，我得到了一个语法错误，错误如下


# 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;

mysql sql-update

来源：https://stackoverflow.com/questions/52626691/syntax-error-while-trying-to-run-a-update-query-in-mysql

2条答案

按热度按时间

0mkxixxg1#

以后需要指定集合，如下所示：

UPDATE TABLEA a 
   JOIN TABLEB b ON a.join_colA = b.join_colB  
   SET a.columnToUpdate = [something]

 UPDATE abc_drupal.civicrm_address 
    join abc_abc_drupal_civi_4_17.civicrm_address inner join abc_drupal.civicrm_address
    on abc_abc_drupal_civi_4_17.civicrm_address.id = abc_drupal.civicrm_address.id  
    SET abc_drupal.civicrm_address.state_province_id = abc_abc_drupal_civi_4_17.civicrm_address.state_province_id
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.contact_id IS NOT NULL

赞(0）回复(0）举报 2021-06-21

rqqzpn5f2#

mysql放置 JOIN 在 UPDATE 条款，而不是通过单独的 FROM （the） 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;

赞(0）回复(0）举报 2021-06-21

我来回答

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

2条答案

相关问题

热门标签

最新问答