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.
you can't use table aliasing and must use the full table name in the NOT EXISTS part of the query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments