Delete using Inner Joins

mysql sql-server inner-join sql-delete

10,865

What you posted works fine for SQL Server; for MySQL below should do the job

DELETE tableA
FROM tableA
INNER JOIN tableB ON tableB.fieldA = tableA.fieldA;

10,865

Author by

Updated on June 04, 2022

David almost 2 years
So I have this correlated subquery, and I was reading that this is not the most efficient way to go about this. Hence, I want to convert this query to an INNER JOIN query.
```
DELETE FROM tableA
WHERE EXISTS (
           SELECT fieldA
           FROM tableB
           WHERE tableB.fieldA= tableA.fieldA)
```
I tried something like this:
```
DELETE a 
FROM TableA a
INNER JOIN TableB b
ON a.fieldA = b.fieldA
```
Which resulted in an Error while executing SQL query on database 'DB': near "a": syntax error

All of my search results on here yielded approximately the same query (similar to what I have tried)
- Evgeny almost 7 years
  
  stackoverflow.com/questions/16481379/…
- Jens almost 7 years
  
  Mysql or SQL-Server?
- David almost 7 years
  
  @Jens both please
- Sean Lange almost 7 years
  
  What you posted should work perfectly in sql server.
- Tim Biegeleisen almost 7 years
  
  @SeanLange Maybe that error came from MySQL?
- Sean Lange almost 7 years
  
  @TimBiegeleisen I am guessing it must have but the OP states they want code for both. What they posted will work in sql server.
- David almost 7 years
  
  @SeanLange Yeah but if there is a common solution.
Sagar Gangwal almost 7 years

@rahul Do you think it's make any effect?? Even though with alias also works fine.