CODEWITHSUNDEEP
mysqlsql
Marc
Marc

Reputation: 1360

Efficiency when deleting rows in two tables

I have table1(id_table1) and table2(id_table2, id_table1). I'd like to remove records in table2 (under a given condition) but then also remove items in table1 that have no more relationships to table2. What is the most efficient way to do that in SQL? I'm using mySql.

Thanks in advance!

Upvotes: 1

Views: 164

Answers (3)

KaKa
KaKa

Reputation: 1553

Assuming you did not set up any cascading deletes and since you asked how to do it in sql, i can see two options:

1)delete from table2 where (condition)

delete from table1 where id not in (select distinct id_table1 from table2)

2)delete from table1 where id in (select distinct id_table1 from table2 where (condition))

delete from table2 where id_table2 not in (select id from table1)

Assuming table2 size is much larger than table1, and the condition considerably shortens the size method 1 scans full table2 once,deletes many records,then scans once method 2 scans full table2 twice

This makes me think method 1 is little bit more efficient if the sizes of tables are very very large.

Upvotes: 0

Johan
Johan

Reputation: 76703

In addition to cularis's answer a less efficient option, if you're using MyISAM you don't have foreign key constraints.

Create a trigger:

DELIMITER $$

CREATE TRIGGER ad_table1_each AFTER DELETE ON table2 FOR EACH ROW
BEGIN
  DELETE FROM table1 WHERE table1.table2_id = OLD.id;
END $$

DELIMITER ;

http://dev.mysql.com/doc/refman/5.5/en/triggers.html
http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

Upvotes: 0

Jacob
Jacob

Reputation: 43269

If you use InnoDB, add a foreign key constraint with an ON DELETE CASCADE. This will automatically delete the rows if the relationship is no longer correct. That way, you don't have to query the database after deleting rows in table2 to check if the relation is still intact.

Foreign key constraints

Upvotes: 7

Related Questions