How to DELETE with INNER JOIN in SQL Server 2008

In SQL Server 2008, the correct syntax for deleting records using an INNER JOIN requires specifying the target table alias after the DELETE keyword rather than using the standard SQL syntax. Your error occurs because SQL Server uses a different syntax pattern than other database systems for DELETE operations with JOINs.

The correct syntax for your query would be:

DELETE wr FROM WorkRecord2 wr INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
WHERE wr.Company = '1' AND wr.Date = '2013-05-06'

Contents

• Understanding the Syntax Difference
• Correct Syntax Structure
• Practical Examples
• Best Practices and Safety Measures
• Common Pitfalls and Troubleshooting
• Alternative Approaches

Understanding the Syntax Difference

SQL Server uses a unique syntax for DELETE operations that involves JOINs. Unlike standard SQL where you might write:

DELETE FROM table1 INNER JOIN table2 ON condition

SQL Server requires you to specify which table’s records you want to delete by placing the table alias directly after the DELETE keyword:

DELETE alias FROM table1 alias INNER JOIN table2 ON condition

This syntax difference is a key reason why you’re encountering the syntax error. The Microsoft SQL Server documentation explains this specific requirement for DELETE operations with JOINs.

Correct Syntax Structure

The basic structure for DELETE with INNER JOIN in SQL Server 2008 is:

DELETE target_alias
FROM target_table target_alias
INNER JOIN related_table related_alias ON join_condition
WHERE additional_conditions;

For your specific case, the corrected query is:

DELETE wr
FROM WorkRecord2 wr
INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
WHERE wr.Company = '1' AND wr.Date = '2013-05-06'

Key Components:

• DELETE wr: Specifies that you’re deleting records from the WorkRecord2 table (aliased as ‘wr’)
• FROM WorkRecord2 wr: The primary table you’re deleting from, with an alias
• INNER JOIN Employee e: The table you’re joining with
• ON wr.EmployeeRun = e.EmployeeNo: The join condition
• WHERE clause: Additional filtering conditions

Practical Examples

Example 1: Deleting Orders for Inactive Customers

DELETE o
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.IsActive = 0;

Example 2: Deleting Sales Records Based on Employee Titles

DELETE m
FROM MySalesPerson m
INNER JOIN Employee e ON m.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Database Administrator';

Example 3: Multi-Table Join Deletion

DELETE d
FROM Department d
INNER JOIN Employee e ON d.id = e.DeptId
INNER JOIN Company c ON d.CompanyID = c.ID
WHERE c.Name = 'Acme Corp' AND e.Status = 'Terminated';

These examples follow the same pattern and demonstrate how the syntax works across different scenarios.

Best Practices and Safety Measures

Always Test with SELECT First

Before executing any DELETE operation, run a SELECT query with the same JOIN and WHERE conditions to verify exactly which records will be deleted:

SELECT wr.*
FROM WorkRecord2 wr
INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
WHERE wr.Company = '1' AND wr.Date = '2013-05-06';

Use Transactions for Critical Operations

Wrap your DELETE statements in transactions to allow for rollback if needed:

BEGIN TRAN;
DELETE wr FROM WorkRecord2 wr
INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
WHERE wr.Company = '1' AND wr.Date = '2013-05-06';

-- Check results and decide to commit or rollback
IF @@ROWCOUNT > 0
    COMMIT TRAN;
ELSE
    ROLLBACK TRAN;

Consider Performance Implications

For large datasets, consider adding appropriate indexes to the join columns to improve performance:

-- Add indexes if they don't exist
CREATE INDEX IX_WorkRecord2_EmployeeRun ON WorkRecord2(EmployeeRun);
CREATE INDEX IX_Employee_EmployeeNo ON Employee(EmployeeNo);

Common Pitfalls and Troubleshooting

Error: “Incorrect syntax near the keyword ‘INNER’”

This is the error you encountered. The solution is to use the correct SQL Server syntax with the table alias after DELETE.

Error: “Ambiguous column name”

When column names exist in multiple tables, you must qualify them with table aliases:

-- Wrong
DELETE wr FROM WorkRecord2 wr INNER JOIN Employee e ON EmployeeRun = EmployeeNo

-- Correct
DELETE wr FROM WorkRecord2 wr INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo

Performance Issues

If your DELETE operation is slow, consider:

• Adding appropriate indexes
• Breaking the operation into smaller batches
• Performing during off-peak hours

Permission Issues

Ensure your database user has DELETE permissions on the target table:

-- Check permissions
SELECT HAS_PERMS_BY_NAME('WorkRecord2', 'OBJECT', 'DELETE');

Alternative Approaches

Using Subqueries

Instead of JOINs, you can use subqueries:

DELETE FROM WorkRecord2
WHERE Company = '1' AND Date = '2013-05-06'
AND EmployeeRun IN (SELECT EmployeeNo FROM Employee WHERE [some condition]);

Using EXISTS

DELETE FROM WorkRecord2
WHERE Company = '1' AND Date = '2013-05-06'
AND EXISTS (SELECT 1 FROM Employee WHERE Employee.EmployeeNo = WorkRecord2.EmployeeRun);

Using CTE (Common Table Expression)

For more complex scenarios:

WITH RecordsToDelete AS (
    SELECT wr.ID
    FROM WorkRecord2 wr
    INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
    WHERE wr.Company = '1' AND wr.Date = '2013-05-06'
)
DELETE FROM WorkRecord2
WHERE ID IN (SELECT ID FROM RecordsToDelete);

Each approach has its advantages depending on your specific requirements and SQL Server version.

Sources

1. How to Delete using INNER JOIN with SQL Server? - GeeksforGeeks
2. Delete Using INNER JOIN in SQL - Baeldung
3. How to Use JOIN in a DELETE Query in SQL - DBVis
4. How do I execute a DELETE using an INNER JOIN in SQL Server? - WebShineTech
5. How can I delete using INNER JOIN with SQL Server? - devtips.online

Conclusion

The key takeaway is that SQL Server 2008 requires a specific syntax for DELETE operations with INNER JOINs that differs from standard SQL. By placing the table alias directly after the DELETE keyword and using FROM before the first table, you can successfully delete records based on join conditions.

Summary of key points:

• Use DELETE alias FROM table alias INNER JOIN... syntax in SQL Server
• Always test with SELECT before executing DELETE operations
• Consider using transactions for critical data modifications
• The correct syntax for your specific query is: DELETE wr FROM WorkRecord2 wr INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo WHERE wr.Company = '1' AND wr.Date = '2013-05-06'

By following these guidelines and understanding SQL Server’s unique syntax requirements, you can perform complex deletions involving multiple tables safely and efficiently.