How to make a delete query?

Question

Assume I have the following database table structure:

A >- B -< C >- D

where >- is a many-to-one relation and -< one-to-many.

Now I would like to delete all A entries which correspond to an D with a given name.

One might say that

DELETE FROM A JOIN B ON <condition> JOIN C ON <condition> JOIN D ON <condition> WHERE D.name=?

would be a solution. Sadly, it appears that you cannot have a JOIN clause in a DELETE query.

Another approach was to have a sub-query, like this:

DELETE FROM A AS elem WHERE elem.id IN ( SELECT id FROM A JOIN B ON <condition> JOIN C ON <condition> JOIN D ON <condition> WHERE D.name=?);

While this might work in Oracle, MySQL will not let me do this(I cannot make a select on the table I'm about to delete from).

So how should I accomplish this?

To be more precise, I am using Hibernate and it's HQL to create this query. So a JPA/Hibernate solution would be preferred.

Of course, simple SQL will also do(I will try to translate it into HQL).

[This](http://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition) says you can delete with join. — Andrius Naruševičius, Sep 06 '13 at 08:20
@AndriusNaruševičius I have already seen that post. I have tried having a join in the delete query, but it is not working - I am being told that there is an error in my query and that I should consult the manual. In MySQL Workbench the first `join` word is highlighted as wrong. It might be a mysql-only issue. I don't know. Any other suggestions? — Dragos, Sep 06 '13 at 08:34
@DRagos you need `DELETE A FROM A JOIN B ...`, see my answer. — ypercubeᵀᴹ, Sep 06 '13 at 08:35

ypercubeᵀᴹ · Accepted Answer · 2013-09-06T08:28:19.510

3

Yes, you can delete with a join. The syntax is described at the MySQL docs: DELETE

DELETE A                     -- you only need to add `A` here, so it knows
                             -- which table to delete from
FROM A 
  JOIN B ON <condition> 
  JOIN C ON <condition> 
  JOIN D ON <condition> 
WHERE D.name = ? ;

The subquery approach will work, too. If the condition that is used to do the A join B is ON A.xid = B.xid, then you can use:

DELETE FROM A 
WHERE A.xid IN 
      ( SELECT B.xid 
        FROM B 
          JOIN C ON <condition> 
          JOIN D ON <condition> 
        WHERE D.name = ?
      ) ;

but I would'n use this. Subqueries with IN sometimes do not perform well.

Another approach is a correlated EXISTS subquery:

DELETE FROM A 
WHERE EXISTS 
      ( SELECT 1  
        FROM B 
          JOIN C ON <condition> 
          JOIN D ON <condition> 
        WHERE D.name = ?
          AND A.xid = B.xid       -- the condition for "A JOIN B"
      ) ;

edited Sep 06 '13 at 08:28

answered Sep 06 '13 at 08:22

ypercubeᵀᴹ

113,259
19
174
235

Sorry but I haven't used HQL so I provided 3 ways in SQL. I hope one of them, you can translate into HQL. – ypercubeᵀᴹ Sep 06 '13 at 08:31
Thank you! The first query seems to be working. I have tried to find a way to translate it, but I had no success in doing so, at least until now. In terms of efficiency, what approach would you recommend? – Dragos Sep 06 '13 at 09:02
I'd try the JOIN way first. If you have indexes on all the joining columns (and on the `D.name` column), then it will be fine but of course you have to test. The "up" and "downs" of the design (1-to-many, then many-to-1) in the connection between A and D may make the EXISTS version perform better (or far worse!) It probably depends on the distribution of values in the tables and the sizes. – ypercubeᵀᴹ Sep 06 '13 at 09:24
I'd say, test all versions (or just the ones that you manage to translate into HQL) and if you run into performance problems, post a new question, here or at the [DBA.SE](http://dba.stackexchange.com/) site. – ypercubeᵀᴹ Sep 06 '13 at 09:27

How to make a delete query?

1 Answers1