Delete using Inner Joins

Question

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)

https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server — Evgeny, Jul 17 '17 at 13:38
@TimBiegeleisen I am guessing it must have but the OP states they want code for both. What they posted will work in sql server. — Sean Lange, Jul 17 '17 at 13:44

score 2 · Accepted Answer · answered Jul 17 '17 at 13:50

2

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;

answered Jul 17 '17 at 13:50

Rahul

76,197
13
71
125

@rahul Do you think it's make any effect?? Even though with alias also works fine. – Sagar Gangwal Jul 17 '17 at 13:57

Delete using Inner Joins

1 Answers1