Delete data from more tables in one query

Question

Please, could you help me, how to delete data from more than 1 table in just one query? I have this tables structure:

products
texts
files

products_files - here are mapped files to products - product_id, file_id texts_files - here are mapped files to texts - text_id, file_id

And I need to do this: If I delete file with id 50, I need to delete all rows from products_files and texts_files where file_id = 50.

Do you know, how to do it?

I tried to use left join, but without any results...

$query = 'DELETE products_files, texts_files FROM products_files 
                    LEFT JOIN texts_files ON texts_files.file_id = products_files.file_id
                  WHERE products_files.file_id = '.$id.' OR texts_files.file_id = '.$id.'';

Maybe this help you: see this http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query — , Dec 21 '14 at 20:21
possible duplicate of [How to delete from multiple tables in MySQL?](http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) — maja, Dec 21 '14 at 20:26
@BrianTompsett-汤莱恩 Fair enough. Can burnination be done slowly? — Strawberry, Aug 27 '15 at 19:04

score 0 · Answer 1 · answered Dec 21 '14 at 20:18

0

You might need ON DELETE CASCADE functionality. However, use it with care as it might have surprising consequences:

http://www.mysqltutorial.org/mysql-on-delete-cascade/

If you add ON DELETE CASCADE in your create table-query, you can delete any item from it. If there are any foreign key constraints, mysql automatically follows the connection and also deletes entries in the other table.

If you wanna stay on the safe side though (recommended), you will need to use multiple delete statements.

answered Dec 21 '14 at 20:18

maja

17,250
17
82
125

On delete cascade is connected with foreign keys, isn't it? I don't use it yet. – Jan Kožušník Dec 21 '14 at 20:21
@JanKožušník Yes it is. If you don't have foreign keys, you need to make multiple delete statements. It's not possible to delete items from multiple statements with only one delete. – maja Dec 21 '14 at 20:24
Thank you. But what about joining tables...? There is no way? – Jan Kožušník Dec 21 '14 at 20:26
@JanKožušník Ok, forget my comment. It is possible to delete from multiple tables: http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql – maja Dec 21 '14 at 20:26

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

Try this:

DELETE f1,f2 
FROM products_files f1 
INNER JOIN texts_files f2 ON f1.file_id = f2.file_id
WHERE f1.file_id = '.$id.';

OR

use MySQL Foreign Key constraints
MySQL foreign key constraints, cascade delete

edited May 23 '17 at 12:29

Community

1
1

answered Dec 21 '14 at 20:19

Parag Tyagi

8,780
3
42
47

score 0 · Answer 3 · answered Dec 21 '14 at 20:19

0

you can specify which table to delete from by prefixing a * with the table name: DELETE table.* FROM table1 JOIN table2 JOIN ... WHERE ... The WHERE clause specifies which rows to delete.

answered Dec 21 '14 at 20:19

Andras

2,995
11
17

So if I want to delete from products_files and texts_files, my query should looks like: `DELETE products_files.*, texts_files FROM.*` – Jan Kožušník Dec 21 '14 at 20:20
that's right. I just checked, and the tablename-only syntax does the same thing (as recommended in Para's answer above) – Andras Dec 21 '14 at 20:23

score 0 · Answer 4 · answered Dec 22 '14 at 00:29

0

Essentially your query seems fine to me (except for the LEFT JOIN) - does this not work...

DELETE pf
     , tf 
  FROM products_files pf
  JOIN texts_files tf
    ON tf.file_id = pf.file_id
 WHERE pf.file_id = $id;

answered Dec 22 '14 at 00:29

Strawberry

33,750
13
40
57

Delete data from more tables in one query

4 Answers4