Why this SQL DELETE query using INNER JOIN is not working?

Question

I am trying to perform this SQL DELETE query that involves some INNER JOIN. I want delete only from the main table named Market_Commodity_Price_Series. I am using MySql.

This is my query:

DELETE
FROM Market_Commodity_Price_Series AS MCPS
INNER JOIN MarketDetails_CommodityDetails AS MDCD
      ON MCPS.market_commodity_details_id = MDCD.id
INNER JOIN MarketDetails AS MD
      ON MDCD.market_details_id = MD.id
WHERE MD.market_name = "Kimironko"

The problem is that performing this query I obtain the following error message:

#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 'MCPS INNER JOIN MarketDetails_CommodityDetails AS MDCD ON MCPS.market_co' at line 2

The "strange" thing is that the SELECT * version of this query works fine, I obtain the records that I expect.

I want to use the delete version to delete these records only from the main query specified by the FROM clause.

What is wrong? What am I missing? How can I fix this error?

score 2 · Answer 1 · answered Mar 13 '18 at 14:28

You need to specify from which table you want the record to be deleted: (Note the table alias after DELETE)

DELETE MCPS
FROM Market_Commodity_Price_Series AS MCPS
INNER JOIN MarketDetails_CommodityDetails AS MDCD
      ON MCPS.market_commodity_details_id = MDCD.id
INNER JOIN MarketDetails AS MD
      ON MDCD.market_details_id = MD.id
WHERE MD.market_name = "Kimironko"

Why this SQL DELETE query using INNER JOIN is not working?

1 Answers1