0

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);

Veiko
  • 1
  • 2

2 Answers2

2

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Correct me if I am wrong but you need to remove e1 that is next to DELETE, you are not selecting so that e1 will result to an error – Demeteor May 03 '22 at 08:43
  • @Demeteor I believe you may be wrong, see [this SO question](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) for an example using the syntax I have used in my answer. – Tim Biegeleisen May 03 '22 at 08:47
  • You seem to be right, I did not know you could also do it this way, I never used it like this since you are basically telling it right after from where to delete. but its always good to learn new stuff cheers ! – Demeteor May 03 '22 at 08:51
0

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);
Demeteor
  • 1,193
  • 2
  • 17
  • 33