How do I delete record from a single table in a query with many joins and a condition?

Question

Having the following related tables

I need to eliminate the relationships between table4 and table1, that is, the data that exists in table1_table4 with the condition that table4's target is different from table3's target

To identify the number of affected rows I wrote this query

SELECT 
    COUNT(*)
FROM
    table2
        INNER JOIN
    table2_table3 ON table2.id = table2_table3.table2_id
        INNER JOIN
    table3 ON table3.id = table2_table3.table3_id
        INNER JOIN
    table1_table2 ON table2.id = table1_table2.table2_id
        INNER JOIN
    table1 ON table1.id = table1_table2.table1_id
        INNER JOIN
    table1_table4 ON table1.id = table1_table4.table1_id
        INNER JOIN
    table4 ON table4.id = table1_table4.table4_id
WHERE
    table3.target != table4.target;

Here I identify 149 records to be affected.

Now I need to delete the affected data in table1_table4. Could you please advise me how should I write this deletion query?

Thanks in advance

ScaisEdge · Answer 1 · 2018-09-27T19:15:46.247

1

You could use the same join as in your select

    DELETE t2.* 
    FROM table2 t2
    INNER JOIN
        table2_table3 ON table2.id = table2_table3.table2_id
            INNER JOIN
        table3 ON table3.id = table2_table3.table3_id
            INNER JOIN
        table1_table2 ON table2.id = table1_table2.table2_id
            INNER JOIN
        table1 ON table1.id = table1_table2.table1_id
            INNER JOIN
        table1_table4 ON table1.id = table1_table4.table1_id
            INNER JOIN
        table4 ON table4.id = table1_table4.table4_id
    WHERE     table3.target != table4.target;

and for table1_table4 , if the join conditions are the same, you could use

    DELETE table1_table4.*
    FROM table1_table4 
    INNER JOIN
        table2_table3 ON table2.id = table2_table3.table2_id
            INNER JOIN
        table3 ON table3.id = table2_table3.table3_id
            INNER JOIN
        table1_table2 ON table2.id = table1_table2.table2_id
            INNER JOIN
        table1 ON table1.id = table1_table2.table1_id
            INNER JOIN
        table1_table4 ON table1.id = table1_table4.table1_id
            INNER JOIN
        table4 ON table4.id = table1_table4.table4_id
    WHERE     table3.target != table4.target;

edited Sep 27 '18 at 19:15

answered Sep 27 '18 at 19:04

ScaisEdge

131,976
10
91
107

Thanks for answering, I need to delete from `table1_table4`. Do I need to move `table1_table4` `INNER JOIN` reference to the begin of `FROM` clause? – Mario Sep 27 '18 at 19:08
answer updated .. for table4 ( make a safe copy of your data test before try to delete) – ScaisEdge Sep 27 '18 at 19:10
In your answer I see you are removing data from `table2` and `table4` but what I need is to remove the data in `table1_table4` – Mario Sep 27 '18 at 19:13
anyway the rule for delete is based or the same from and join conditio for the select .. and the clause delete tablename.* from – ScaisEdge Sep 27 '18 at 19:18
Interestingly the deletion query affected 119 records, when the query selection shows 149 records. Even more curious is that once the records have been deleted, I execute the selection query, return 1 and wait for 30. Some suggestion? – Mario Sep 27 '18 at 20:07
what do you mean with "the selection query, return 1 and wait for 30" ? (wait for 30?) explain better .. .. remember that you are deleting only the table table1_table4.. could be that some of the rows in this table hane more then on row join in others tables .. – ScaisEdge Sep 28 '18 at 05:39

Caius Jard · Answer 2 · 2018-09-27T20:23:02.503

1

Mysql supports multicolumn IN

delete from table1_table4 where (table1_id, table4_id) in (
 select t14.table1_id, t14.table4_id from
    table2
    INNER JOIN
    table2_table3 ON table2.id = table2_table3.table2_id
    INNER JOIN
    table3 ON table3.id = table2_table3.table3_id
    INNER JOIN
    table1_table2 ON table2.id = table1_table2.table2_id
    INNER JOIN
    table1 ON table1.id = table1_table2.table1_id
    INNER JOIN
    (SELECT * FROM table1_table4) t14 ON table1.id = t14.table1_id
    INNER JOIN
    table4 ON table4.id = t14.table4_id
)

I'm not a huge fan of using IN for long lists, but this is relatively short

You'll also notice that your table1_table4 is wrapped in another select. This is a fudge to get past the restriction that MySQL doesn't let you modify a table you selected from. This subquery materialises the table as a temporary one, so mySQL sees it as a different table from the one you're updating. As a trick it probably won't work in MySQL 8 - see the link posted in the first comment for more info

edited Sep 27 '18 at 20:23

answered Sep 27 '18 at 19:07

Caius Jard

72,509
5
49
80

This won't work because of https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Barmar Sep 27 '18 at 19:47
Thanks for the pointer; i've "fixed" that, but please leave your comment as i reference it in the question – Caius Jard Sep 27 '18 at 20:23
I just tried your query, I am experimenting the same behavior scaisEdge query, the delete query affect 119 records, but my count query in the question count 149 rows, Any idea why I am getting this result? Thanks – Mario Sep 27 '18 at 21:20
Duplicate rows? `DELETE FROM table WHERE (a,b) IN (1,2),(1,2)` will only delete one row even though there are two in the IN.. (if a,b is the primary key of course). Try doing `SELECT DISTINCT table1_table4.*` instead of `SELECT COUNT(*)` in your 149 rows query, and see if it still give 149 rows – Caius Jard Sep 27 '18 at 22:02

How do I delete record from a single table in a query with many joins and a condition?

2 Answers2