mysql delete with negative join

Anon

helo,

i have a little problem with a mysql-query:

i have 2 tables, lets say table A and B, which are connected via an ID. table A holds some data, and the ID is unique for every line in table A. table B holds additional information. table B can have various lines with the same ID.
what i want to do is to delete every line in table B that has an ID which is not listed in table A.

i can select those lines via something like:
"select B.ID from B left join A on B.ID = A.ID where A.ID is NULL"

i dont want to save these numbers, for a second delete statement. is there a way to do an instant delete on this lines???

thanks for your help...

[deleted]

"is there a way to do an instant delete on this lines???"

No.

This reverse-engineering, trying to make the database consistent again because B contains orphaned data that nolonger has a relation to A.

This is what PK/FK relations were invented for. If you use PostgreSQL/oracle (well basically any database except MySQL) you can define and enforce PK/FK relations with casscading delete. Then when you delete an element from A, all corresponding elements from B are deleted automatically.

MySQL sucks, and I don't say that without good reason. 🙂

A forum, a FAQ, email notification, what else do you need?