Deleting Data From Multiple Tables

Question

Suppose,I've a table called UNIVERSITY containing universities name:

universityID    universityNAME  isACTIVE
     7            GNDU             1
     6            PU               1
     5            PTU              1
     8            LPU              1

Now these universities ID's has been(obviously) used in many tables within the database(name e.g.Education),Suppose 10 tables.

Q.Now what happen if i delete one university?

A.The universityID field in other tables becomes NULL.

But I don't want these,rather when I delete 1 university from UNIVERSITY TABLE,all its occurrences with Rows in all 10 table should get deleted.

What will be the shortest and easiest MySQL Query for this operation.

NOTE:I'm using PHP language.

Answer 1

If you have foreign keys to this table, add the option ON DELETE CASCADE. Then when you delete a row in a parent table all the rows in the child tables that refference that row will be deleted as well.

Answer 2

If all the tables are MyISAM or if all the tables are InnoDB and they do not have any foreign keys references, I got just the thing for you:

NOT EVERY TABLE HAS universityID = 7

DELETE
    U.*,tbl01.*,tbl02.*,tbl03.*,tbl04.*,tbl05.*,
    tbl06.*,tbl07.*,tbl08.*,tbl09.*,tbl10.*
FROM
UNIVERSITY U
LEFT JOIN tbl01 USING (universityID)
LEFT JOIN tbl02 USING (universityID)
LEFT JOIN tbl03 USING (universityID)
LEFT JOIN tbl04 USING (universityID)
LEFT JOIN tbl05 USING (universityID)
LEFT JOIN tbl06 USING (universityID)
LEFT JOIN tbl07 USING (universityID)
LEFT JOIN tbl08 USING (universityID)
LEFT JOIN tbl09 USING (universityID)
LEFT JOIN tbl10 USING (universityID)
WHERE U.universityID = 7
AND tbl01.universityID IS NOT NULL
AND tbl02.universityID IS NOT NULL
AND tbl03.universityID IS NOT NULL
AND tbl04.universityID IS NOT NULL
AND tbl05.universityID IS NOT NULL
AND tbl06.universityID IS NOT NULL
AND tbl07.universityID IS NOT NULL
AND tbl08.universityID IS NOT NULL
AND tbl09.universityID IS NOT NULL
AND tbl10.universityID IS NOT NULL;

EVERY TABLE HAS universityID = 7

DELETE
    U.*,tbl01.*,tbl02.*,tbl03.*,tbl04.*,tbl05.*,
    tbl06.*,tbl07.*,tbl08.*,tbl09.*,tbl10.*
FROM
UNIVERSITY U
INNER JOIN tbl01 USING (universityID)
INNER JOIN tbl02 USING (universityID)
INNER JOIN tbl03 USING (universityID)
INNER JOIN tbl04 USING (universityID)
INNER JOIN tbl05 USING (universityID)
INNER JOIN tbl06 USING (universityID)
INNER JOIN tbl07 USING (universityID)
INNER JOIN tbl08 USING (universityID)
INNER JOIN tbl09 USING (universityID)
INNER JOIN tbl10 USING (universityID)
WHERE U.universityID = 7;

Give it a Try !!!

Answer 3

You can do this something like that

$query = "SELECT universityID FROM UNIVERSITY WHERE universityNAME='PTU'";
$result = mysql_query($query);
$id = mysql_fetch_assoc($result);

//Create an array of tables from which you want to delete university reference
$tables = array('xyz','abc','test');

foreach($tables as $table) {
     $query = "DELETE FROM $table WHERE universityID=$id->universityID";
     mysql_query($query);
}