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'
This question is related to
sql
sql-server
sql-server-2008
inner-join
sql-delete
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 the 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';
Another way is using 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
Note: We cannot use JOIN
inside CTE
when you want to delete
.
Here's what I currently use for deleting or even, updating:
DELETE w
FROM WorkRecord2 w,
Employee e
WHERE w.EmployeeRun = e.EmployeeNo
AND w.Company = '1'
AND w.Date = '2013-05-06'
DELETE a FROM WorkRecord2 a
INNER JOIN Employee b
ON a.EmployeeRun = b.EmployeeNo
Where a.Company = '1'
AND a.Date = '2013-05-06'
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 the same procedure, for example:
DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf
This is a simple query to delete the records from two table at a time.
DELETE table1.* ,
table2.*
FROM table1
INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'
You could even do a sub-query. Like this code below:
DELETE FROM users WHERE id IN(
SELECT user_id FROM Employee WHERE 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"
Try this query:
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';
You don't specify the tables for Company
and Date
, and you might want to fix that.
Standard SQL using MERGE
:
MERGE WorkRecord2 T
USING Employee S
ON T.EmployeeRun = S.EmployeeNo
AND Company = '1'
AND Date = '2013-05-06'
WHEN MATCHED THEN DELETE;
The answer from Devart is also standard SQL, though incomplete. It should look more like this:
DELETE
FROM WorkRecord2
WHERE EXISTS ( SELECT *
FROM Employee S
WHERE S.EmployeeNo = WorkRecord2.EmployeeRun
AND Company = '1'
AND Date = '2013-05-06' );
The important thing to note about the above is it is clear the delete is targeting a single table, as enforced in the second example by requiring a scalar subquery.
For me, the various proprietary syntax answers are harder to read and understand. I guess the mindset for is best described in the answer by frans eilering, i.e. the person writing the code doesn't necessarily care about the person who will read and maintain the code.
This version should work:
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee ON EmployeeRun=EmployeeNo
Where Company = '1' AND Date = '2013-05-06'
Here is my SQL Server version
DECLARE @ProfileId table(Id bigint)
DELETE FROM AspNetUsers
OUTPUT deleted.ProfileId INTO @ProfileId
WHERE Email = @email
DELETE FROM UserProfiles
WHERE Id = (Select Id FROM @ProfileId)
Try this:
DELETE FROM WorkRecord2
FROM Employee
Where EmployeeRun=EmployeeNo
And Company = '1'
AND Date = '2013-05-06'
It is possible this will 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 ....
)
Source: Stackoverflow.com