CODEWITHSUNDEEP
mysqlsql
Memen
Memen

Reputation: 17

Delete data table with left join

I do not understand MySQL delete when I need to delete data in a table with data from another table that depend on it.

For example if I want to delete a data in table 'factory', all data at table 'room' that depends on data at table 'factory is also deleted'.

Fac_ID is the primary key in 'factory' and foreign key in 'room'

below is my SQL code.

DELETE * FROM factory 
LEFT JOIN room ON room.Fac_ID = factory.Fac_ID 
WHERE factory.Fac_ID = :Fac_ID

Can any one help me?

Upvotes: 1

Views: 261

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

MySQL allows you to delete rows from multiple tables at the same time. The syntax is:

DELETE f, r
    FROM factory f LEFT JOIN
         room r
         ON r.Fac_ID = f.Fac_ID 
    WHERE f.Fac_ID = :Fac_ID;

However, this is better set up as a cascading delete foreign key relationship between the two tables.

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

There is a small mistake in your query because of which I think you are facing problem.

As per my understanding you have some records in Main table and some records in refrenced table. There are some case for which main table has some id but there is not entry in refrence table for that id. And for handling that case you applied left join.

But in your query you wrote reference table on left so basically it is taking all of the record from reference table which is kind of inner join in this case.

So for correcting this you need to interchange the key id pass in your query or you may use right join to select all records from main table.

DELETE * FROM factory 
LEFT JOIN room ON factory.Fac_ID = room.Fac_ID    --- here you applied left join   
WHERE factory.Fac_ID = :Fac_ID

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

I think you need a separate delete for this.

First is to delete foreign data

delete from room where Fac_ID = :Fac_ID

Then delete primary data

delete from factory where Fac_ID = :Fac_ID

Unless your table design is ON DELETE CASCADE (supported only in INNODB), you only need to delete the primary data

Upvotes: 2

Related Questions