How to Delete Using INNER JOIN in SQL Server

The correct syntax for deleting records using INNER JOIN in SQL Server 2008 requires specifying the table to delete from before the FROM clause. Your current query fails because SQL Server expects the table name to delete immediately after DELETE, not after FROM. The proper syntax is: DELETE WorkRecord2 FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun = EmployeeNo WHERE Company = '1' AND Date = '2013-05-06'.

Contents

• Understanding the Syntax Error
• Correct DELETE INNER JOIN Syntax
• Working Examples
• Common Mistakes and Troubleshooting
• Best Practices
• Alternative Approaches

Understanding the Syntax Error

The error “Incorrect syntax near the keyword ‘INNER’” occurs because SQL Server’s DELETE statement has a specific syntax requirement when using JOIN operations. In your original query:

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

The issue is that SQL Server expects the table name to delete immediately after the DELETE keyword, not after FROM. As Sentry.io explains, “you can delete from only one table at a time in SQL” and the syntax must clearly specify which table is the target of the deletion operation.

SQL Server’s parser encounters the INNER JOIN keyword where it doesn’t expect it, leading to the syntax error. This is a common mistake when developers try to apply the same JOIN syntax used in SELECT statements to DELETE operations.

Correct DELETE INNER JOIN Syntax

The correct syntax for using INNER JOIN in a DELETE statement in SQL Server 2008 is:

DELETE table_name 
FROM table_name 
INNER JOIN other_table ON join_condition
WHERE filter_conditions

Key components of this syntax:

• DELETE table_name: Specifies which table to delete records from
• FROM table_name: Repeats the table name after FROM (required in SQL Server)
• INNER JOIN other_table: Joins with another table for filtering
• ON join_condition: Specifies the join condition
• WHERE filter_conditions: Additional filtering for which records to delete

Here’s how your query should be corrected:

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

As GeeksforGeeks confirms, “In SQL Server, we can use INNER JOIN within a DELETE statement to remove data from one table based on matching records in another table.” The key is that only one table can be the target of the deletion.

Working Examples

Example 1: Basic Inner Join Delete

DELETE a
FROM CUSTOMERS AS a
INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

This example, from Tutorialspoint, deletes customers whose salary is less than 2000.00, based on matching records in the ORDERS table.

Example 2: Using Table Aliases

DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.column = t1.column
WHERE t2.some_value = 'criteria';

This syntax, mentioned on SQLServerCentral, uses aliases to make the query more readable and maintainable.

Example 3: Multiple Join Conditions

DELETE WorkRecord2
FROM WorkRecord2 wr
INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
INNER JOIN Companies c ON e.CompanyID = c.CompanyID
WHERE c.CompanyCode = '1' 
    AND wr.WorkDate = '2013-05-06';

This example extends your original scenario to include an additional join condition, demonstrating how you can filter records based on multiple tables.

Common Mistakes and Troubleshooting

Mistake 1: Incorrect Syntax Order

Incorrect:

DELETE FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE condition

Correct:

DELETE Table1 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE condition

Mistake 2: Trying to Delete from Multiple Tables

Some developers try to syntax like:

DELETE Table1, Table2
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

This is not supported in SQL Server. You can only delete from one table at a time, though you can use joins to filter which records to delete.

Mistake 3: Missing Table Name After DELETE

Incorrect:

DELETE
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

Correct:

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

Troubleshooting Tips

1. Test with SELECT first: Before performing the actual delete, test your join conditions with a SELECT statement to ensure you’re targeting the right records.

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

2. Use transactions: Wrap your delete operations in transactions to allow rollback if needed.

3. Check constraints: As mentioned in Stack Overflow, be aware of foreign key constraints that might prevent deletion.

4. Consider performance: Large delete operations with joins can be resource-intensive. Consider breaking them into smaller batches.

Best Practices

1. Always Test with SELECT

Before executing a DELETE with JOIN, always verify your join conditions and filters using a SELECT statement. This helps prevent accidental deletion of the wrong records.

2. Use Table Aliases

Always use table aliases in JOIN operations to make your queries more readable and to avoid ambiguity:

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

3. Be Explicit About Which Table to Delete

Even though your query only deletes from one table, make it clear in your code which table is the target of the deletion operation.

4. Consider Performance for Large Operations

For large datasets, consider using batch processing:

DECLARE @BatchSize INT = 1000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) wr
    FROM WorkRecord2 wr
    INNER JOIN Employee e ON wr.EmployeeRun = e.EmployeeNo
    WHERE e.Company = '1' AND wr.Date = '2013-05-06';
    
    SET @RowsAffected = @@ROWCOUNT;
END

5. Document Your Delete Operations

Always add comments to explain why records are being deleted and provide context for future maintenance.

Alternative Approaches

1. Using Subqueries

Instead of JOIN, you can use subqueries for the same result:

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

2. Using EXISTS

The EXISTS operator can also be used for similar filtering:

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

3. Using CTE (Common Table Expression)

For more complex scenarios, you can use CTEs:

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

Each approach has its advantages, but the DELETE with INNER JOIN syntax is often the most readable and straightforward for simple join conditions.

Conclusion

The key to successfully deleting records using INNER JOIN in SQL Server 2008 is understanding the proper syntax requirements. Remember these important points:

1. Always specify the table to delete from immediately after the DELETE keyword
2. Repeat the table name after the FROM clause
3. Use INNER JOIN to filter records based on conditions in other tables
4. Test your join conditions with SELECT statements before executing DELETE operations
5. Consider performance implications for large datasets

By following the correct syntax DELETE table_name FROM table_name INNER JOIN other_table ON join_condition WHERE filter_conditions, you can effectively delete records from one table based on matching conditions in another table. Always be cautious with delete operations and consider using transactions, testing with SELECT first, and documenting your queries to ensure data integrity and maintainability.

Sources

1. How can I delete using INNER JOIN with SQL Server? - Stack Overflow
2. How can I delete using INNER JOIN with SQL Server? | Sentry
3. How to delete data in SQL Server using an INNER JOIN query | codeshare
4. Delete Using INNER JOIN in SQL - Baeldung
5. How to Delete using INNER JOIN with SQL Server? - GeeksforGeeks
6. Delete con inner join en SQL Server | Dataprix
7. DELETE using INNER JOIN – SQLServerCentral Forums
8. How to Use JOIN in a DELETE Query in SQL - dbVis
9. SQL DELETE with JOIN - Dofactory
10. SQL - DELETE JOIN - Tutorialspoint