Início / dba / Perguntas / 29047
Accepted
Grijesh Chauhan
Asked: 2012-11-22 03:16:46 +0800 CST

Como atualizar a mesma tabela na exclusão no MYSQL?

  • 772

No meu banco de dados eu tenho uma tabela : Employeecom associação recursiva, um funcionário pode ser chefe de outro funcionário.

A seguir está a consulta que usei para criar a tabela: 

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

E eu poderia criar minha tabela com sucesso: 

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Em seguida, inseri algumas tuplas na tabela Employee usando a seguinte consulta.

mysql> INSERT INTO Employee VALUES 
    -> ("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")
    -> ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

Agora tenho a seguinte relação hierárquica entre as linhas da tabela: 

        A
       / \
      B   F
     / \
    C   D
       / \
      G   E

A seguir está a instrução Select para a tabela:

mysql> 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)

Agora, quero impor uma restrição como: If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS). por exemplo, se eu excluir D, torne- Bse BOSS de Ge E.

Para isso também escrevi um Trigger da seguinte forma: 

mysql>  DELIMITER $$
mysql>        CREATE
    ->        TRIGGER `Employee_before_delete` BEFORE DELETE
    ->          ON `Employee`
    ->          FOR EACH ROW BEGIN
    ->          UPDATE Employee
    ->          SET MSSN=old.MSSN
    ->          WHERE MSSN=old.MSSN; 
    ->        END$$
Query OK, 0 rows affected (0.07 sec)

mysql>        DELIMITER ;

Mas quando realizo alguma exclusão: 

mysql> DELETE FROM Employee WHERE SSN='4';
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger
because it is already used by statement which invoked this stored 
function/trigger.

Eu aprendothis trigger is not possible aqui porque In MySQL triggers can't manipulate the table they are assigned to.

Existe alguma outra maneira possível de fazer isso? Alguém pode me sugerir outro método? Uma sugestão seria suficiente, mas deve ser eficiente.

EDIT: É possível usar Nested Query?
Também perguntei aqui .

mysql mysql-5.5

1 respostas

  1. Best Answer

    A maneira mais fácil de excluir Dé fazer o seguinte:

    • Ter Bherdado Ge E, colocando-os no mesmo nível que D.
    • ExcluirD

    Primeiro, carregarei seus dados de amostra

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

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

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

mysql> INSERT INTO Employee VALUES
    -> ("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");
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

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

    Ver todos os relacionamentos atuais

    mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| 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>

    Com esta UPDATE JOINconsulta

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

    vamos elevar Ge Epara o mesmo nível De mostrar relacionamentos

    mysql> UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> 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> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| 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>

    Neste ponto, Dninguém é chefe.

    Agora, exclua-o e mostre os relacionamentos:

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

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| 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>

    De uma chance !!!

    Observe que você não precisa de um gatilho

    • 2

relate perguntas