How to delete rows in a mySQL table specified by data from the same table (in one expression)?

Question

I want to delete rows from a table "Book", where two colums have appeared before.

I successfully selected the ids of the rows wich should be deleted like so:

SELECT all_dupes.book_id 
               FROM   (SELECT * 
                       FROM   Book as BBook  NATURAL JOIN Book as BBBook
                       WHERE  book_id NOT IN (SELECT book_id 
                                              FROM   Book as BBook NATURAL JOIN Book as BBBook
                                              GROUP  BY buying_price, 
                                                        selling_price 
                                              HAVING Count(*) = 1 
                                              ORDER  BY book_id) 
                       ORDER  BY book_id) AS all_dupes 
               WHERE  book_id NOT IN (SELECT book_id 
                                      FROM   Book as BBook NATURAL JOIN Book as BBBook
                                      GROUP  BY buying_price, 
                                                selling_price 
                                      HAVING Count(*) >= 2 
                                      ORDER  BY book_id);

…but when I try to delete the rows with

DELETE FROM Book
WHERE  book_id IN (
               <expression as above without tailing ;>   
               ) ;

I get an error ERROR 1093 (HY000): Table 'Book' is specified twice, both as a target for 'DELETE' and as a separate source for data

I already tried to alias the table and to natural join the table to itself, like suggested in other questions regarding this issue.

Also I read quite some ammount of questions here, but they mostly are very specific and I don't get how to change my delete-query by the answers provided there.

What do I have to change in order to get this done? Splitting the expression is not an option (meaning there mustn't be two ;, but just one expression).

Database used: MariaDB

Answer 1

There are a few problems with your SQL. I would fix those first, even though your DBMS isn't rejecting the query. It might fix the problem, because in your DELETE statement you might have finally pushed the system past its limit. In any case it will clarify the question.

1. ORDER BY is, in standard SQL, permitted only once, in the outermost SELECT clause. It is a way to return the rows to the calling process in a particular order, not a way to express order internally to the SQL processor. Your extra ORDER BYs don't affect your query, so remove them.

2. GROUP BY should repeat any column names not aggregated in the SELECT clause. Because you select book_id, you should also group by book_id.

I doubt you actually need all those joins anyway. I'm not sure what you're trying to do, but I think your query might just be

delete Book
where exists ( select 1
      from Book as B
      where B.book_id = Book.book_id
      group by B.book_id, B.buying_price, B.selling_price
      having count(*) > 1
)

That would eliminate all rows with a book_id for which any combination of {book_id, buying_price, selling_price} is not unique. But I'm not sure that's what you really want.

I want to delete rows from a table "Book", where two colums have appeared before.

Yeah, there is no "before" in SQL, because there's no order. I think what you mean is that if you have 3 "duplicate" rows, you'd like to eliminate the extra 2. SQL has no such operation.

SQL operates by predicate logic: rows are deleted according to whether or not they match some criteria. Duplicate rows, by definition, all meet the same criteria. Because there's no order, there's no notion of deleting all those that match except the first one.

The best solution, it must be said, is to prevent duplication in the first place by correctly declaring uniqueness in the table definition. Failing that, the remedy is usually to insert the distinct set into a temporary table, delete in the main table all those that exist in the temporary one, insert from the temporary into the main, and drop the temporary table.