Ok I'm sure this is something every novice runs into at one point or another. I have tables A and B with a many to many relationship. I need to delete rows from table A when a certain condition is met, but the condition needs to include B. I don't actually want to delete anything from B though.
DELETE FROM a
WHERE a.id = b.id
AND b.characteristic = 'y';
That is what I thought would work, but MySQL gave me an error "Unknown table 'b' in the where clause". I'm assuming it's because I didn't list it in the FROM clause.
Can somebody help me out with this? I'm sure there's a simple solution that I've overlooked. The MySQL docs for JOIN only cover SELECTs, not DELETEs. Ugh.