Elegant way to remove orphan rows?

You could use cascading with foreign keys to accomplish this. In the following example, any time a row is deleted from A, or an A_ID in A is changed, this change will automatically be reflected in table B. You can read more on foreign keys in the MySql Documentation.

CREATE TABLE A(
   A_ID INT, 
   PRIMARY_KEY(A_ID)
) TYPE=InnoDB;

CREATE TABLE B(
   B_ID INT,
   A_ID INT,
   CONSTRAINT FK_B_A FOREIGN KEY REFERENCES A(A_ID) ON DELETE CASCADE ON UPDATE CASCADE,
   PRIMARY_KEY(B_ID, A_ID)
) TYPE=InnoDB;

How about:

DELETE FROM history_table 
WHERE customer_id NOT IN (SELECT customer_id FROM customer);

delete from history_table where customer_id not in (select customer_id from customers)

did you mean something like this?


DELETE h.* FROM history h
LEFT JOIN customer c ON h.customer_id = c.id
WHERE c.id IS NULL

I'm typing this from the top of my head, but you get the idea hopefully.

Delete syntax documentation

Tags:

Mysql