I am working with a database table that stores, among other things, an AssociateID field and a DocumentName field. The table is used to keep records of which associates are missing which documents.
我正在使用一个数据库表,其中存储一个AssociateID字段和一个DocumentName字段。该表用于记录哪些员工缺少哪些文档。
I am trying to write a stored procedure that will remove a row for 'Restrictive Covenant Letters' if an entry for 'Termination Statement' cannot be found for the same associate. Below is the body of what I have so far:
我正在尝试编写一个存储过程,如果找不到同一个联系人的“终止声明”条目,则会删除“限制性约定字母”的行。以下是我到目前为止的主体:
DELETE from tbl_HR_Auditing_Reports
WHERE DocumentName = 'Restrictive Covenant Letters'
AND NOT EXISTS
(
SELECT TS.AssociateID
FROM
(SELECT AssociateID FROM tbl_HR_Auditing_Reports WHERE DocumentName = 'Termination Statement (*)') TS,
(SELECT AssociateID FROM tbl_HR_Auditing_Reports WHERE DocumentName = 'Restrictive Covenant Letters') RCL
WHERE TS.AssociateID = RCL.AssociateID
)
I think I am close, but sql isn't really my forte. If anyone could possibly help, I would really appreciate it!
我觉得我很亲密,但是sql并不是我的强项。如果有人可以提供帮助,我真的很感激!
1 个解决方案
#1
2
According to the MySQL manual:
根据MySQL手册:
Currently, you cannot delete from a table and select from the same table in a subquery.
目前,您无法从表中删除并从子查询中的同一表中进行选择。
To get around this you can use a temporary table, inserting the relevant rows into the temporary table, then referencing it in the delete statement.
要解决此问题,您可以使用临时表,将相关行插入临时表,然后在delete语句中引用它。
CREATE TEMPORARY TABLE tmp (AssociateID INT);
INSERT INTO tmp (AssociateID)
SELECT AssociateID
FROM tbl_HR_Auditing_Reports
WHERE DocumentName = 'Termination Statement (*)';
DELETE
FROM tbl_HR_Auditing_Reports
WHERE DocumentName = 'Restrictive Covenant Letters'
AND NOT EXISTS
( SELECT 1
FROM tmp
WHERE tmp.AssociateID = tbl_HR_Auditing_Reports.AssociateID
)
关于SQL小提琴的例子
#1
2
According to the MySQL manual:
根据MySQL手册:
Currently, you cannot delete from a table and select from the same table in a subquery.
目前,您无法从表中删除并从子查询中的同一表中进行选择。
To get around this you can use a temporary table, inserting the relevant rows into the temporary table, then referencing it in the delete statement.
要解决此问题,您可以使用临时表,将相关行插入临时表,然后在delete语句中引用它。
CREATE TEMPORARY TABLE tmp (AssociateID INT);
INSERT INTO tmp (AssociateID)
SELECT AssociateID
FROM tbl_HR_Auditing_Reports
WHERE DocumentName = 'Termination Statement (*)';
DELETE
FROM tbl_HR_Auditing_Reports
WHERE DocumentName = 'Restrictive Covenant Letters'
AND NOT EXISTS
( SELECT 1
FROM tmp
WHERE tmp.AssociateID = tbl_HR_Auditing_Reports.AssociateID
)
关于SQL小提琴的例子