mysql - 为什么这个复杂的删除 SQL 查询不起作用？

Question

我正在尝试实现一个“复杂”的 SQL 删除查询，但它不起作用。

这是我的查询:

DELETE
FROM Market_Commodity_Price_Series     AS MC_PS
INNER JOIN MarketDetails_CommodityDetails AS MD_CD
      ON MC_PS.market_commodity_details_id = MD_CD.id 
INNER JOIN MarketDetails                     AS MD
      ON  MD_CD.market_details_id = MD.id  
WHERE
      MD.localization_id = 1

我只想删除 Market_Commodity_Price_Series 中的所有行，但具有此 WHERE 条件。正如您所看到的，where 条件使用一些 JOIN 操作。

问题是执行此查询时我收到此错误消息:

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS MC_PS INNER JOIN MarketDetails_CommodityDetails AS MD_CD ON MC_PS.mar' at line 2

我该如何解决这个问题？也许使用它作为子查询:

SELECT MC_PS.id
FROM Market_Commodity_Price_Series     AS MC_PS
INNER JOIN MarketDetails_CommodityDetails AS MD_CD
      ON MC_PS.market_commodity_details_id = MD_CD.id 
INNER JOIN MarketDetails                     AS MD
      ON  MD_CD.market_details_id = MD.id  
WHERE
      MD.localization_id = 1

(使用 ID)

但如何呢？

Answer 1

您想删除MC_PS，因此请在删除丢失的“MC_PS”后写入

DELETE MC_PS
FROM Market_Commodity_Price_Series     AS MC_PS
INNER JOIN MarketDetails_CommodityDetails AS MD_CD
      ON MC_PS.market_commodity_details_id = MD_CD.id 
INNER JOIN MarketDetails                     AS MD
      ON  MD_CD.market_details_id = MD.id  
WHERE
      MD.localization_id = 1