Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
If you have 10 tables with Foreign Keys that reference University, change the 10 FKs to have the attribute ON DELETE CASCADE. –  ypercube Nov 7 '12 at 7:15
    
And use InnoDB. –  dezso Nov 7 '12 at 10:28

3 Answers 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);
}
share|improve this answer

If all the tables are MyISAM or if all the tables are InnoDB and no foreign keys references, I got just the thing for you:

If you are not sure that all 10 tables have universityID = 7, do this (I mught be wrong on this one):

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;

If you are totally sure that all 10 tables have universityID = 7, do this:

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 !!!

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.