Delete data from a single table from inner join result

Question

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.

Other than using `DELETE` *instead of* `SELECT`? – Ignacio Vazquez-Abrams Sep 21 '16 at 02:42 — Ignacio Vazquez-Abrams, Sep 21 '16 at 02:42
How do you know it doesn't work? – shmosel Sep 21 '16 at 03:18 — shmosel, Sep 21 '16 at 03:18

score 0 · Accepted Answer · edited May 23 '17 at 12:16

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)

Rana Aalamgeer · Answer 2 · 2016-09-21T06:13:21.007

0

If you want to delete whole records of form table name borrowing_mat_master_data and only material_tools_group column from table name material_tools_master_data use following query i hope it will help you.

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

edited Sep 21 '16 at 06:13

answered Sep 21 '16 at 04:40

Rana Aalamgeer

692
2
7
22

Rana, any DELETE statement will always delete **rows** (if any are found). – Rene Sep 21 '16 at 06:09

Delete data from a single table from inner join result

2 Answers2