我想在SQL Server 2008中使用INNER JOIN
来进行删除操作。
但是我遇到了这个错误:
Msg 156, Level 15, State 1, Line 15
关键字'INNER' 附近的语法错误。
我的代码:
DELETE
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06'
我想在SQL Server 2008中使用INNER JOIN
来进行删除操作。
但是我遇到了这个错误:
Msg 156, Level 15, State 1, Line 15
关键字'INNER' 附近的语法错误。
我的代码:
DELETE
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06'
您需要指定从哪个表中删除数据。以下是带有别名的版本:
DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'
只需在 DELETE
和 FROM
之间添加要删除记录的表名,因为我们必须指定要删除的表。同时移除 ORDER BY
子句,因为在删除记录时不需要排序。
所以你最终的查询应该像这样:
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06';
这可能会对您有所帮助 -
DELETE FROM dbo.WorkRecord2
WHERE EmployeeRun IN (
SELECT e.EmployeeNo
FROM dbo.Employee e
WHERE ...
)
或者试试这个 -
DELETE FROM dbo.WorkRecord2
WHERE EXISTS(
SELECT 1
FROM dbo.Employee e
WHERE EmployeeRun = e.EmployeeNo
AND ....
)
试试这个:
DELETE FROM WorkRecord2
FROM Employee
Where EmployeeRun=EmployeeNo
And Company = '1'
AND Date = '2013-05-06'
在SQL Server Management Studio中,我可以轻松创建一个SELECT
查询:
SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
我可以执行它,而且我的所有联系人都会显示出来。
现在将 SELECT
更改为 DELETE
:
现在将 SELECT
更改为 DELETE
:
DELETE Contact
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
您在SELECT
语句中看到的所有记录都将被删除。
您甚至可以使用相同的过程创建一个更加困难的内连接,例如:
DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf
应该是:
DELETE zpost
FROM zpost
INNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid)
WHERE zcomment.icomment = "first"
你甚至可以做一个子查询。像下面的代码:
DELETE FROM users WHERE id IN(
SELECT user_id FROM Employee WHERE Company = '1' AND Date = '2013-05-06'
)
尝试这个查询:
DELETE WorkRecord2, Employee
FROM WorkRecord2
INNER JOIN Employee ON (tbl_name.EmployeeRun=tbl_name.EmployeeNo)
WHERE tbl_name.Company = '1'
AND tbl_name.Date = '2013-05-06';
CTE
:;WITH cte
AS (SELECT *
FROM workrecord2 w
WHERE EXISTS (SELECT 1
FROM employee e
WHERE employeerun = employeeno
AND company = '1'
AND date = '2013-05-06'))
DELETE FROM cte
注意:当您想要删除
时,不能在CTE
中使用JOIN
。
这是我目前用于删除或更新的代码:
DELETE w
FROM WorkRecord2 w,
Employee e
WHERE w.EmployeeRun = e.EmployeeNo
AND w.Company = '1'
AND w.Date = '2013-05-06'
DELETE
与连接一起使用。 - Pondlife