主页 / dba / 问题 / 29047
Accepted
Grijesh Chauhan
Asked: 2012-11-22 03:16:46 +0800 CST

如何在 MYSQL 中删除时更新同一张表？

  • 772

在我的数据库中，我有一个表：Employee通过递归关联，员工可以成为其他员工的老板。

以下是我用来创建表的查询： 

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;

我可以成功地创建我的表： 

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)

然后我使用以下查询在 Employee 表中插入了一些元组。

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

现在我在表中的行之间具有以下层次关系： 

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

以下是表的 Select 语句：

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)

现在，我想施加一个约束，例如 : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS)。例如，如果我删除D，则B成为Gand的 BOSS E。

为此，我还编写了一个触发器，如下所示： 

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 ;

但是当我执行一些删除时： 

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.

我在这里学习是this trigger is not possible因为In MySQL triggers can't manipulate the table they are assigned to.

还有其他可能的方法吗？有人可以建议我其他方法吗？一个建议就足够了，但应该是有效的。

编辑：可以使用Nested Query吗？
我也在这里问过。

mysql mysql-5.5

1 个回答

  1. Best Answer

    最简单的删除方法D是执行以下操作：

    • 具有B继承G和E，将它们与 置于同一级别D。
    • 删除D

    首先，我将加载您的示例数据

    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>

    查看所有当前关系

    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>

    有了这个UPDATE JOIN查询

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

    让我们提升到G和显示关系E相同的水平D

    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>

    在这一点上，D是没有人的老板。

    现在，删除它并显示关系：

    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>

    试试看 ！！！

    请注意，您不需要触发器

    • 2

相关问题