How to Delete using INNER JOIN with SQL Server?
How to Delete using INNER JOIN with SQL Server?
Question
I want to delete using INNER JOIN
in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE FROM WorkRecord2
INNER JOIN Employee ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'
Accepted Answer
You need to specify what table you are deleting from, here is a version with an alias:
DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'
Read more… Read less…
Just add the name of the table between DELETE
and FROM
from where you want to delete records because we have to specify the table to delete. Also remove ORDER BY
clause because there is nothing to order while deleting records.
So your final query should be like this:
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06';
Possible this be helpful for you -
DELETE FROM dbo.WorkRecord2
WHERE EmployeeRun IN (
SELECT e.EmployeeNo
FROM dbo.Employee e
WHERE ...
)
Or try this -
DELETE FROM dbo.WorkRecord2
WHERE EXISTS(
SELECT 1
FROM dbo.Employee e
WHERE EmployeeRun = e.EmployeeNo
AND ....
)
Try this:
DELETE FROM WorkRecord2
FROM Employee
Where EmployeeRun=EmployeeNo
And Company = '1'
AND Date = '2013-05-06'
It should be:
DELETE zpost
FROM zpost
INNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid)
WHERE zcomment.icomment = "first"
In SQL Server Management Studio I can easily create a SELECT
query.
SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
I can execute it, and all my contacts are shown.
Now change the SELECT
to a DELETE
:
DELETE Contact
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
All the records you saw in the SELECT
statement will be removed.
You may even create a more difficult inner join with he same procedure, for example:
DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf