M Ansyori M Ansyori - 2 months ago 5
MySQL Question

Delete data from a single table from inner join result

I have this query in 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


I want to delete the result from the query above, I've tried this query below but it doesn't work.

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'
)
);


Any help will be much appreciated.

Regards.

Answer

In order to delete data from borrowing_mat_master_data table out of the joined result:

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'
    );

In order to delete data both from borrowing_mat_master_data and material_tools_master_data table out of the joined result:

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'
    )

See Delete with join (single table, multiple tables)