DELETE from TABLE using EXISTS from the same table-CodePudding

I have duplicate MYSQL tables. I'd like to delete duplicates. But MySQL gives an error message. Does MYSQL and MARIA DB not support SQL statements that run on Oracle?

delete from ESTONIA E where exists(select 1 from ESTONIA x where x.city=E.city and x.id<>E.id);

CodePudding user response：

You could instead use a delete self join:

DELETE e1
FROM ESTONIA e1
INNER JOIN ESTONIA e2
    ON e2.city = e1.city AND
       e2.id <> e1.id;

CodePudding user response：

Exist does Exist in MySQL see bellow

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Source Click Here

But I believe in your case you will need to use IN instead of EXIST

DELETE 
 FROM table_name
 WHERE column_name IN
(SELECT column_name FROM table_name WHERE condition);