In theory, the query should delete the entries from the catalog table and product_to_catalog , where catalog.id and product_to_catalog.catalog_id same and find the entries in the product table with product.id = product_to_catalog.product_id and also delete them. What I made a mistake?

 DELETE c.*,pc.*,p.* FROM `catalog` c LEFT JOIN `product_to_catalog` pc ON c.id = pc.catalog_id LEFT JOIN `product` p ON p.id = pc.product_id WHERE c.id = 2
  • Did you really get rid of .* After table synonyms? Just judging by the syntax of the delete command and test queries - they are absolutely equivalent. - ApInvent

3 answers 3

It is necessary after DELETE list the name of those tables from which the data is deleted, it is not necessary to write table_name.* , This is still not a query for selecting data.

 DELETE c, pc, p FROM `catalog` c LEFT JOIN `product_to_catalog` pc ON c.id = pc.catalog_id LEFT JOIN `product` p ON p.id = pc.product_id WHERE c.id = 2

    If you make a model example, then your request perfectly removes everything. Sql Fiddle example.

    Given that the query uses LEFT JOIN , records can only be deleted from the catalog table if no other records are found in other tables.

    If you look at the syntax of the delete command:

     DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]

    That there is no difference between c.*,pc.*,p.* And c, pc, p .

      Similar question (with solution):

      Slowly working or deleting from several tables does not work.

      I think the answer from there answers this one. Well, and comments (to the answer) look.