Search code examples
mysqlsql-serverinner-joinsql-delete

Delete using Inner Joins

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)

Solution

  • 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;