SQL cross delete rows

Question

I consider myself quite competent with MySQL but can't find a good solution for this one. Simplified, two tables, an adjective has four different declensions:

--adjectives--
word_id INT
decl_m_id INT
decl_f_id INT
decl_n_id INT
decl_pl_id INT

--declensions--
id INT
nominative VARCHAR(100)
genitive VARCHAR(100)
dative VARCHAR(100)
...

Now I want to delete a few nouns and have to code

wordIds = ...
declsM  = sql( "SELECT decl_m_id FROM adjectives WHERE word_id IN ?", wordIds )
declsF  = sql( "SELECT decl_f_id FROM adjectives WHERE word_id IN ?", wordIds )
declsN  = sql( "SELECT decl_n_id FROM adjectives WHERE word_id IN ?", wordIds )
declsPl = sql( "SELECT decl_pl_id FROM adjectives WHERE word_id IN ?", wordIds )
sql( "DELETE FROM adjectives WHERE word_id IN ?", wordIds )
sql( "DELETE FROM declensions WHERE id IN ?", declsM+declsF+declsN+declsPl )

Isn't there a simpler way to do this?

Look into defining the foreign key relationship with `on delete cascade`. — Gordon Linoff, Jan 16 '15 at 22:22
I'm afraid that works only the other way around, e.g. I would have to delete one of the declinations to dismiss the adjective. Seems dirty to me.. — Michel H., Jan 16 '15 at 22:29

score 0 · Accepted Answer · edited May 23 '17 at 12:27

You can use DELETE along with JOIN, and delete rows from two tables at once. Assuming I understood the relations between those tables, you could do something like this:

$sql = "DELETE adjectives.*, declensions.* 
FROM adjectives adj
JOIN declensions decl ON 
   (adj.decl_m_id = decl.id OR
   adj.decl_f_id = decl.id OR
   adj.decl_n_id = decl.id OR
   adj.decl_pl_id = decl.id)
WHERE adj.word_id IN ?";

This way you'll delete all rows from adjectives, that have word_id in the given set, and also delete all declensions relating to that word_id.

I have not tested it myself, but I have used similar queries myself (in case cascading delete is not always desired).

This answer provides a clear example how to delete rows from 2 tables at once.

Wow, I've never seen that but it works, thank you very much! Also for the link. — Michel H., Jan 16 '15 at 22:41

SQL cross delete rows

1 Answers1