Posted by J.O. Aho on 01/25/07 06:33

Kenoli wrote:
> I have 4 tables (Let's say: t1, t2, t3, t4). One has a person_id
> column as a native key and the others have a person_id column as a
> foreign key.
>
> Periodically, I want to remove all records in all tables with a given
> person_id value. The MYSQL manual seems to say I should enter the
> tables twice like this:
>
> DELETE t1 t2 t3 t4 FROM t1 t2 t3 t4 WHERE . . . (which makes no sense
> to me)
>
> Then I'm not sure how to indicate the person_id value for all tables.
> I'd like to just do:
>
> DELETE t1 t2 t3 t4 FROM t1 t2 t3 t4 WHERE person_id = '69'
>
> But this doesn't work. Do I need to do something like:
>
> DELETE t1 t2 t3 t4 FROM t1 t2 t3 t4 WHERE t1.person_id = '69' AND
> t2.person_id = '69' etc.
>
> At any rate, I've tried various combinations here and haven't found
> anything that works.

From the manual:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

which leads to

DELETE FROM t1, t2, t3 USING t1, t2, t3, t4 WHERE t1.person_id=t2.person_id
AND t2.person_id=t3.person_id AND t3.person_id=t4.person_id AND t1.person_id='69';

--

//Aho

[Back to original message]