phpmyadmin Question posted in
The official documentation can be found here.

Error #1093 – Table is specified twice, both as a target for 'DELETE' and as a separate source for data – Phpmyadmin

I’m trying to delete duplicate rows in a table. The email field needs to be be unique in the table for the code after it to run. Here’s my code:

DELETE FROM deleteRequests
    WHERE Email NOT IN (
        SELECT MAX(Email)
        FROM deleteRequests
        GROUP BY email);

The error I get is: ‘#1093 – Table ‘deleteRequests’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data’

I think the problem is that I’m using mariadb 10.2 (which also means I can’t use WITH CTE). I am not in control of updating this, so I need a workaround. Suggestions?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up creating a new table to work from.

    CREATE TABLE deleteRequestsWorking as
SELECT MAX(mamaID) as mamaID, Email
          FROM deleteRequests
          GROUP BY mamaID;

    This gave me the table I needed, and so instead of trying to get it to update my original table by using a LEFT JOIN and DELETE, I just used it. They're both temporary tables that get deleted at the end of the process, so it works.


  2. 0

    MySQL, MariaDB doesn’t allow the table to be referenced again in a DELETE or UPDATE. An easy work-around is to use a JOIN:

    DELETE dr
    FROM deleteRequests dr LEFT JOIN
         (SELECT MAX(email) as max_email
          FROM deleteRequests
          GROUP BY email
         ) dr2
         ON dr2.email = dr.max_email
    WHERE dr2.max_email IS NOT NULL;

    Of course, this query is non-sensical because you are aggregating by email and choosing the max — so all non-NULL emails will match.

    Perhaps you want HAVING COUNT(*) = 1 in the subquery,.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search