How to Update same table on deletion in MYSQL?
The easiest way to delete D is to do the following:


Have B inherit G and E, putting them on the same level as D.
Delete D


First, I'll load your sample data

mysql&gt; DROP DATABASE IF EXISTS grijesh;
Query OK, 1 row affected (0.06 sec)

mysql&gt; CREATE DATABASE grijesh;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE grijesh
Database changed
mysql&gt; CREATE TABLE IF NOT EXISTS `Employee` (
    -&gt;   `SSN` varchar(64) NOT NULL,
    -&gt;   `name` varchar(64) DEFAULT NULL,
    -&gt;   `designation` varchar(128) NOT NULL,
    -&gt;   `MSSN` varchar(64) DEFAULT NULL,
    -&gt;   PRIMARY KEY (`SSN`),
    -&gt;   CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
    -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql&gt; INSERT INTO Employee VALUES
    -&gt; ("1", "A", "OWNER",  NULL),
    -&gt; ("2", "B", "BOSS",   "1"),
    -&gt; ("3", "C", "WORKER", "2"),
    -&gt; ("4", "D", "BOSS",   "2"),
    -&gt; ("5", "E", "WORKER", "4"),
    -&gt; ("6", "F", "WORKER", "1"),
    -&gt; ("7", "G", "WORKER", "4");
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
+-----+------+-------------+------+
7 rows in set (0.00 sec)

mysql&gt;


View all current relationships

mysql&gt; SELECT
    -&gt;     A.name,A.designation,
    -&gt;     B.name,B.designation
    -&gt; FROM
    -&gt;     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -&gt; ORDER BY A.SSN,B.SSN
    -&gt; ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| D    | BOSS        | E    | WORKER      |
| D    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql&gt;


With this UPDATE JOIN query

UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
SET B.MSSN = A.MSSN WHERE A.SSN = '4';


let's elevate G and E to the same level as D and show relationships

mysql&gt; UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -&gt; SET B.MSSN = A.MSSN WHERE A.SSN = '4';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql&gt; SELECT
    -&gt;     A.name,A.designation,
    -&gt;     B.name,B.designation
    -&gt; FROM
    -&gt;     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -&gt; ORDER BY A.SSN,B.SSN
    -&gt; ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql&gt;


At this point, D is nobody's boss.

Now, delete it and show relationships:

mysql&gt; DELETE FROM `Employee` WHERE SSN = '4';
Query OK, 1 row affected (0.05 sec)

mysql&gt; SELECT
    -&gt;     A.name,A.designation,
    -&gt;     B.name,B.designation
    -&gt; FROM
    -&gt;     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -&gt; ORDER BY A.SSN,B.SSN
    -&gt; ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
5 rows in set (0.00 sec)

mysql&gt;


Give it a Try !!!

Please notice that you do not need a Trigger