Joining on deletion

Question

I'm working with mysql:

• btableA has tableB_id column
• tableB has some_interestring_column_on_TableB

I want (pseudo sql below):

delete from tableA 
 where the associated row in tableB (via tableB_id) has  
     some_interestring_column_on_TableB = 'interestingValue'

Please help me to translate the pseudo sql into real sql.

Answer 1

MySQL supports JOINs in the DELETE statement, as well as deleting from multiple tables in a single statement. The following will only delete from TABLEA:

DELETE ta
  FROM TABLEA ta
  JOIN TABLEB tb ON b.id = a.tableb_id
                AND b.col = 'some value'

If you wanted to delete from both tables, use:

DELETE ta, tb
  FROM TABLEA ta
  JOIN TABLEB tb ON b.id = a.tableb_id
                AND b.col = 'some value'

That said, this support is very uncommon in other databases -- you'd have to use IN or EXISTS in most cases.