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

本文介绍了如何在MYSQL中删除时更新同一张表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在我的数据库中，有一个表Employee具有递归关联(一个雇员可以是其他雇员的老板):

In my database I have a table Employee that has recursive association (an employee can be boss of other 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)

然后插入:

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

现在我在表中的行之间具有以下层次关系(所有者>老板>工人):

Now I have following hierarchical relation (owner > boss > worker) among the rows in table:

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

以下是表的Select语句:

Following is Select statement for table:

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成为G和E的BOSS.

Now, I want to impose a constraint like : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS). e.g. If I delete D then B Become BOSS of G and E.

为此，我还编写了 Trigger ，如下所示:

For that I also written a Trigger as follows:

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 ;

但是当我执行一些删除操作时:

But When I perform some deletion:

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吗?有人可以建议我其他方法吗?一个建议就足够了，但应该是有效的.

Is there some other possible way to do this? Is it possible using Nested Query? Can some one suggest me other method ? A suggestion would be enough but should be efficient.

编辑: 我得到了答案: 代替触发，可以使用stored procedure或two consecutive queries. 第一和秒.

EDIT: I got answers: Instead of trigger a stored procedure or two consecutive queries is possible. First and second.

我为这个问题编写的解决方案如下，运作良好！:

The Solution I wrote for this problem as below, Working Well!:

• 我正在为MYSQL version older then 5.5编写的一个辅助信号功能.

• A a helper signal function as I am writing for MYSQL version older then 5.5.

DELIMITER//

DELIMITER // CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255)) BEGIN SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1'); PREPARE my_signal_stmt FROM @sql; EXECUTE my_signal_stmt; DEALLOCATE PREPARE my_signal_stmt; END//

• 用于从Employee表中删除员工的存储过程.
• A Stored Procedure to delete employee from Employee Table.
CREATE PROCEDURE delete_employee(IN dssn varchar(64)) BEGIN DECLARE empDesignation varchar(128); DECLARE empSsn varchar(64); DECLARE empMssn varchar(64); SELECT SSN, designation, MSSN INTO empSsn, empDesignation, empMssn FROM Employee WHERE SSN = dssn; IF (empSsn IS NOT NULL) THEN CASE WHEN empDesignation = 'OWNER' THEN CALL my_signal('Error: OWNER can not deleted!'); WHEN empDesignation = 'WORKER' THEN DELETE FROM Employee WHERE SSN = empSsn; WHEN empDesignation = 'BOSS' THEN BEGIN UPDATE Employee SET MSSN = empMssn WHERE MSSN = empSsn; DELETE FROM Employee WHERE SSN = empSsn; END; END CASE; ELSE CALL my_signal('Error: Not a valid row!'); END IF; END//

DELIMITER;

DELIMITER ;

推荐答案

使用存储过程:

UPDATE b SET b.mssn = a.mssn FROM EMPLOYEE a JOIN EMPLOYEE b ON b.mssn = a.ssn WHERE a.ssn = @deletedBoss DELETE FROM employee WHERE ssn = @deletedBoss

使用存储过程，您可以简单地删除所需的行，然后更新同一张表.那应该可以防止错误消息.

With a stored procedure, you can simply delete the rows you want, and after that, update the same table. That should prevent the error message.