Reason: liquibase.exception.DatabaseException with DELETE statement in MySQL 5.7

Question

I have been trying to execute the below query in Mysql 5.7.

DELETE FROM tablename1 T1
WHERE EXISTS (
SELECT *
FROM tablename1 T2
WHERE T2.code = T1.code
AND T2.dname = T2.dname
AND T2.created_date > T1.created_date
);

But it throws me an error : Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'T1

This query works fine in Oracle but for some reason 'alias' doesnt work in MySQL 5.7 Is there anyway I can fix this issue within this Mysql 5.7 version.

Thanks in advance.

Possible duplicate of [Get around self-refencing in a DELETE query](https://stackoverflow.com/questions/4606915/get-around-self-refencing-in-a-delete-query) — Uueerdo, Oct 05 '18 at 23:36

score 1 · Accepted Answer · answered Oct 05 '18 at 23:30

I am fairly sure the issue is not the alias, but subquerying the table you are deleting from, try this instead.

DELETE T1
FROM tablename1 AS T1 
INNER JOIN tablename1 AS T2 
   ON T2.code = T1.code
   AND T2.dname = T2.dname
   AND T2.created_date > T1.created_date
;

I'm not positive it will work either, since it is still referencing the delete subject twice; but it is worth trying.

Reason: liquibase.exception.DatabaseException with DELETE statement in MySQL 5.7

1 Answers1