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