mysql - 复杂查询中的语法错误

Question

UPDATE emr_cwagreich.patient_medication a
INNER JOIN
(
    SELECT emr_cwagreich.patient_medication_archive.id AS id,
        emr_cwagreich.patient_medication_archive.drug_syn_id AS drug_syn_id, 
        pk.NDC10 AS ndc10, 
        pk.NDC11 AS ndc11, 
        pk.PackageID AS package_id, 
        pd.ProductID AS product_id, 
        pd.MarketedProductID AS marketed_product_id,
        pgns.GenericNameLong AS medication_name
    FROM emr_cwagreich.patient_medication_archive
    JOIN ep_api.CORE_GENDRUG_SYNONYM cgs ON emr_cwagreich.patient_medication_archive.drug_syn_id = cgs.DRUG_SYN_ID
    JOIN ep_api.NDC_PKG_PRODUCT npp ON cgs.DRUG_SYN_ID = npp.DRUG_SYN_ID
    JOIN ep_alchemy.package pk ON npp.CORE_10 = pk.NDC10
    JOIN ep_alchemy.product pd ON pk.ProductID = pd.ProductID
    JOIN ep_alchemy.product_generic_name_stub pgns ON pd.ProductID = pgns.ProductID
    GROUP BY medication_name
    ORDER BY COUNT(pgns.GenericNameLong) DESC
    LIMIT 1
) b ON a.id = b.id
SET a.ndc10 = b.ndc10,
    a.ndc11 = b.ndc11,
    a.package_id = b.package_id,
    a.product_id = b.product_id,
    a.marketed_product_id = b.marketed_product_id,
    a.medication_name = b.medication_name
WHERE (b.drug_syn_id <> "" AND b.drug_syn_id IS NOT NULL)
AND
(a.product_id = "" OR a.product_id IS NULL)
AND 
a.id = 17221 

上面的查询有语法错误。我的数据库管理工具 (HeidiSQL) 告诉我第 2 行附近有一个语法错误，但我一辈子都无法弄清楚它在哪里......

对任何得到这个的人来说都是巨大的支持。

致法比奥:错误是(逐字)

SQL Error (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
'UPDATE emr_cwagreich.patient_medication a INNER JOIN (SELECT emr_cwagrei' at line 2

我已将其简化为内部联接语句中的子选择(就此而言，我通过删除联接来降低了该子选择的复杂性)。这是:

SELECT pma.id AS id,
    pma.drug_syn_id AS drug_syn_id, 
    pk.NDC10 AS ndc10, 
    pk.NDC11 AS ndc11, 
    pk.PackageID AS package_id, 
    pk.ProductID AS product_id,
    pgns.GenericNameLong AS medication_name
FROM emr_cwagreich.patient_medication_archive pma
JOIN ep_api.CORE_GENDRUG_SYNONYM cgs ON pma.drug_syn_id = cgs.DRUG_SYN_ID
JOIN ep_api.NDC_PKG_PRODUCT npp ON cgs.DRUG_SYN_ID = npp.DRUG_SYN_ID
JOIN ep_alchemy.package pk ON npp.CORE_10 = pk.NDC10
JOIN ep_alchemy.product_generic_name_stub pgns ON pk.ProductID = pgns.ProductID
GROUP BY medication_name
ORDER BY COUNT(pgns.GenericNameLong) DESC
LIMIT 1

此查询不返回语法错误。只是花了很长时间(即使我已经在外键上放置了索引......)

Answer 1

看起来您的 SQL 语句的布局不太正确。

您不能在 UPDATE 语句之后将表 emr_cwagreich.Patent_medication 别名为 a，它需要稍后出现在 之后>SET 语句。尝试重新排列您的查询，如下所示:

UPDATE  a
  SET   a.ndc10 = b.ndc10,
        a.ndc11 = b.ndc11,
        a.package_id = b.package_id,
        a.product_id = b.product_id,
        a.marketed_product_id = b.marketed_product_id,
        a.medication_name = b.medication_name
  FROM  emr_cwagreich.patient_medication a
    INNER JOIN
...

请注意在 SET 之后添加 FROM 语句，此时您可以为更新表添加别名并连接到其他表。