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