从内部联接结果中删除单个表中的数据

Question

我在mysql中有这个查询。

SELECT
    a.*, b.material_tools_group
FROM
    borrowing_mat_master_data AS a
INNER JOIN material_tools_master_data AS b ON a.tools_code = b.material_code
WHERE
    material_tools_group IN (
        'Consumable',
        'Material'
    )
ORDER BY
    `a`.`tools_code` ASC

我想从上面的查询中删除结果，我已经尝试了下面的查询，但是它不起作用。

DELETE
FROM
    borrowing_mat_master_data
WHERE
    tools_code IN (
        SELECT
            a.*, b.material_tools_group
        FROM
            borrowing_mat_master_data AS a
        INNER JOIN material_tools_master_data AS b ON a.tools_code = b.material_code
        WHERE
            material_tools_group IN (
                'Consumable',
                'Material'
            )
    );

任何帮助都将不胜感激。

致以问候。

Answer 1

为了从联合结果：borrowing_mat_master_data 表中删除表中的数据：

DELETE a
FROM
    borrowing_mat_master_data AS a
INNER JOIN material_tools_master_data AS b ON a.tools_code = b.material_code
WHERE
    material_tools_group IN (
        'Consumable',
        'Material'
    );

为了从联合结果中删除borrowing_mat_master_data material_tools_master_data 和material_tools_master_data表中的数据：

DELETE a,b
FROM
    borrowing_mat_master_data AS a
INNER JOIN material_tools_master_data AS b ON a.tools_code = b.material_code
WHERE
    material_tools_group IN (
        'Consumable',
        'Material'
    )

请参阅Delete with join (single table, multiple tables)

Answer 2

如果您想删除表单表名borrowing_mat_master_data的全部记录，并且只删除表名material_tools_master_data中的material_tools_group列，请使用以下查询，我希望它将对您有所帮助。

DELETE a FROM borrowing_mat_master_data AS a
INNER JOIN material_tools_master_data AS b ON a.tools_code = b.material_code
WHERE
    b.material_tools_group IN (
        'Consumable',
        'Material'
    )