My SELECT statement works, but I'm unable to DELETE

Question

I've got a working SQL query that returns information that needs to be removed from the database. I'm having a hard time using this information to delete the records.

I'm using a mySQL database and I'm trying to make these changes using HeidiSQL.

The code that works to select the data is:

SELECT * 
FROM users u 
WHERE NOT EXISTS 
    (SELECT 1 
     from users_classdetails uc 
     WHERE u.id = uc.userID 
         AND dateEntered > DATE_SUB(NOW(),INTERVAL 3 YEAR) ) 
AND bestcontact = ""

And I hoped to be able to use something like:

DELETE 
FROM users u 
WHERE NOT EXISTS 
    (SELECT 1 
     from users_classdetails uc 
     WHERE u.id = uc.userID
        AND dateEntered > DATE_SUB(NOW(),INTERVAL 3 YEAR) )
AND bestcontact = ""

But I'm shown an error 1064 that explains my syntax is wrong. It points out the WHERE NOT EXISTS ( SELECT 1 from users_classdetails as the problem. I'm not sure why it would be valid for SELECT but not DELETE. Any help would be greatly appreciated.

Can't you use a JOIN instead? http://stackoverflow.com/questions/20462374/delete-all-rows-which-has-no-id-existing-in-another-table — Nathan, Jun 01 '15 at 14:39
possible duplicate of [Delete sql rows where IDs do not have a match in another table](http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table) — AdamMc331, Jun 01 '15 at 14:43
Nath: There is no reason I couldn't use a join. I'll read the link you suggested and give it a try. Thanks. — David, Jun 01 '15 at 14:43
Please try this: DELETE u FROM users u WHERE NOT EXISTS (SELECT 1 from users_classdetails uc WHERE u.id = uc.userID AND dateEntered > DATE_SUB(NOW(),INTERVAL 3 YEAR) ) AND bestcontact = "" — MelgoV, Jun 01 '15 at 14:52

score 4 · Accepted Answer · answered Jun 01 '15 at 14:46

4

you can't use table aliasing and must use the full table name in the NOT EXISTS part of the query.

Source

answered Jun 01 '15 at 14:46

Nathan

1,220
3
15
26

Exactly the answer. Once I used the whole table name instead of using aliasing it worked perfectly. Thank you. – David Jun 01 '15 at 14:55
1

You *can* use table aliases if you use the Multi-table syntax for the MySQL DELETE statement. – spencer7593 Jun 01 '15 at 15:01

score 0 · Answer 2 · answered Jun 01 '15 at 14:55

You can add u.* following the DELETE, and the statement should work for you.

That's the "Multi-table syntax" for the DELETE statement. Reference: [https://dev.mysql.com/doc/refman/5.5/en/delete.html].

That's the same form of the statement you'd need to use if you used an anti-join pattern (as someone suggested in the comments.

Your SELECT statement could have been written

SELECT u.*
  FROM users u
 WHERE ...

To change that into a delete statement, just replace the SELECT keyword with DELETE...

DELETE u.*
  FROM users u
 WHERE ...

To use a JOIN, you'd need an anti-join pattern to emulate the NOT EXISTS. That's a left outer join to return all rows from users, along with matching rows from the other table, and a predicate that excludes all rows that had a match, leaving you with rows from users that don't have a match.

Again, to return rows from users, specify u.* in your SELECT statement. Here's an example of an anti-join pattern that returns a result equivalent to the NOT EXISTS query:

  SELECT u.*
    FROM users u 
    LEFT
    JOIN users_classdetails uc
      ON uc.userID = u.id
     AND uc.dateEntered > NOW() + INTERVAL -3 YEAR
   WHERE uc.userID IS NULL
     AND u.bestcontact = ''

To turn that into a DELETE statement, just replace SELECT with DELETE.

My SELECT statement works, but I'm unable to DELETE

2 Answers2