CODEWITHSUNDEEP
mysqlsql
user
user

Reputation: 695

Is it possible to delete the records having same foreign key in different table with single query?

I need to delete the records in all the multiple table having same foreign key ID with single sql query. I given the example below,

Table Name : basetable
id | column1
---|---------
 1 |  BCol1
 2 |  BCol2

Table Name : Reftable1

id | BaseTableID | Column1
---|-------------|--------
 1 |      1      | RT1Col1
 2 |      1      | RT1Col2

Table Name : Reftable2

id | BaseTableID | Column1
---|-------------|--------
 1 |      2      | RT2Col1
 2 |      1      | RT2Col2

Table Name : Reftable3

id | BaseTableID | Column1
---|-------------|--------
 1 |      1      | RT3Col1
 2 |      2      | RT3Col2

In above three reference table I want to delete records having BaseTableID=1 with single mysql query. Is there is any possibilities please share your idea

Upvotes: 0

Views: 356

Answers (3)

Barmar
Barmar

Reputation: 780724

I think this will do it:

DELETE r1, r2, r3
FROM Reftable1 r1
JOIN Reftable2 r2
JOIN Reftable3 r3
WHERE r1.BaseTableID = 1
AND r2.BaseTableID = 1
AND r3.BaseTableID = 1

SQLFIDDLE

If some tables may not have matching rows, this LEFT JOIN should do it:

DELETE r1, r2, r3
FROM basetable b
LEFT JOIN Reftable1 r1 ON b.id = r1.BaseTableID
LEFT JOIN Reftable2 r2 ON b.id = r2.BaseTableID
LEFT JOIN Reftable3 r3 ON b.id = r3.BaseTableID
WHERE b.id = 1

SQLFIDDLE

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71384

You can delete across a join like this:

DELETE FROM basetable
INNER JOIN Reftable1
  ON basetable.id = Reftable1.BaseTableID
INNER JOIN Reftable2
  ON basetable.id = Reftable2.BaseTableID
INNER JOIN Reftable3
  ON basetable.id = Reftable3.BaseTableID
WHERE basetable.id = ?

Upvotes: 1

smk
smk

Reputation: 5842

You will have to specify on cascade delete when you create the table.

baseTable id references BaseTable(id) on delete cascade

For more info please refer http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Upvotes: 1

Related Questions