Posted by Steve on 01/24/07 22:32

"kenoli" <kenoli.p@gmail.com> wrote in message
news:1169676807.183928.285830@h3g2000cwc.googlegroups.com...
|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.
|
| Please help!

you should be able to specify the person_id as a primary key on one of the
tables, say t1. next, you could specify the person_id in the remaining
tables as the foreign key and specify cascading deletes on those tables.
that way, you'd only need to:

delete from t1 where person_id = 12

all other tables would remove the records having person_id = 12 also.

as for other options, i believe your example is just syntactically wrong.
try:

delete from t1, t2, t3, t4 where person_id = 12

however, i think that's horrible architecture and difficult to maintain in
code. if you do want to go that route, i'd suggest:

$commit = true;
$tables = array('t1', 't2', 't3', 't4');
db::begin();
foreach ($tables as $table)
{
$sql = "
DELETE
FROM " . $table . "
WHERE person_id = '" . $id . "'
";
if (!db::execute($sql))
{
$commit = false;
break;
}
}
switch ($commit)
{
case true : db::commit(); break;
case false : db::rollback(); break;
}

db is a fake db call, btw. while this is more code, it certainly is more
flexible and explicit. it also allows you to, with a slight modification,
delete related records where the foreign key can have any name instead of
just person_id...

$tables = array(
't1' => 'person_id' ,
't2' => 'dude' ,
't3' => 'dudette' ,
't4' => 'munchkin'
);

then it becomes:

foreach ($tables as $table => $column)
{
$sql = "
DELETE
FROM " . $table . "
WHERE " . $column . " = '" . $id . "'
";

}

anyway...that's just me rambling.

[Back to original message]