mysql - 从不同数据库加入时，mysql 中的表/别名不是唯一的

Question

我收到错误 [Err] 1066 - Not unique table/alias: 'vtiger_leadscf'

UPDATE vtigerdb.vtiger_leadscf
JOIN vtiger_new.vtiger_leaddetails ON vtiger_new.vtiger_leadscf.leadid = vtiger_new.vtiger_leaddetails.leadid
JOIN vtiger_new.vtiger_leadaddress ON vtiger_new.vtiger_leadaddress.leadaddressid = vtiger_new.vtiger_leadscf.leadid
JOIN vtiger_new.vtiger_crmentity ON vtiger_new.vtiger_crmentity.crmid = vtiger_new.vtiger_leadscf.leadid
JOIN vtiger_new.vtiger_users ON vtiger_new.vtiger_users.id = vtiger_new.vtiger_crmentity.smcreatorid
JOIN vtigerdb.vtiger_leadscf ON vtigerdb.vtiger_leadscf.leadid = vtiger_new.vtiger_leadscf.leadid
SET vtigerdb.vtiger_leadscf.cf_953 = CONCAT(vtiger_new.vtiger_users.first_name,' ',vtiger_new.vtiger_users.last_name)
WHERE vtigerdb.vtiger_leadscf.leadid = vtiger_new.vtiger_leadscf.leadid
AND cf_953 = ""

无法弄清楚它有什么问题。

Answer 1

上次连接和更新引用同一个表。使用 JOIN table AS other_name 来避免这个问题。

这是更正后的查询。另外，我认为你在最后一次加入时指的是 vtiger_new，而不是你写的 vtigerdb。

UPDATE vtigerdb.vtiger_leadscf
JOIN vtiger_new.vtiger_leaddetails ON vtiger_new.vtiger_leadscf.leadid = vtiger_new.vtiger_leaddetails.leadid
JOIN vtiger_new.vtiger_leadaddress ON vtiger_new.vtiger_leadaddress.leadaddressid = vtiger_new.vtiger_leadscf.leadid
JOIN vtiger_new.vtiger_crmentity ON vtiger_new.vtiger_crmentity.crmid = vtiger_new.vtiger_leadscf.leadid
JOIN vtiger_new.vtiger_users ON vtiger_new.vtiger_users.id = vtiger_new.vtiger_crmentity.smcreatorid
JOIN vtiger_new.vtiger_leadscf AS vlead ON vtigerdb.vtiger_leadscf.leadid = vlead.leadid
SET vtigerdb.vtiger_leadscf.cf_953 = CONCAT(vtiger_new.vtiger_users.first_name,' ',vtiger_new.vtiger_users.last_name)
WHERE vtigerdb.vtiger_leadscf.leadid = vlead.leadid
AND cf_953 = ""