CODEWITHSUNDEEP
mysqlsubqueryinner-join
user3649739
user3649739

Reputation: 1869

Delete using Select Inner Join from Same Table

I am trying to Delete from Table A where the ID exists in a Select Inner Join that includes Table A similar to:

Delete from TableA where ID in 
(Select Distinct A.ID from TableA A
Inner Join TableC C
Inner Join TableJ J
Inner Join Table J J2
On J.VendorID=J2.VendordID
and J.Title=J2.Title
and A.C_ID=C.C_ID
and J.ID=A.J_ID
and J2.ID=C.J_ID)

The Select works like I want (amazingly) but I keep getting the error

1093 - You can't specify target table 'TableA' for update in FROM clause

Which I assume means you can't try to delete from a table that is included in your subquery. Is there anyway to restructure this so I can?

Upvotes: 0

Views: 331

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

You don't need to use a subquery. 

Delete A from TableA A
Inner Join TableC C
Inner Join TableJ J
Inner Join TableJ J2
On J.VendorID = J2.VendordID
and J.Title = J2.Title
and A.C_ID = C.C_ID
and J.ID = A.J_ID
and J2.ID = C.J_ID

Upvotes: 1

Related Questions