SQL delete with exists is not working in MariaDB

I’m running this select in MariaDB and it works as expected, it’s just a select with an exists:

select * from pred_loan_defaults  d
where exists (select 1 from pred_loan_defaults d2 
where d.exec_id = d2.exec_id and d.loan_identifier = d2.loan_identifier 
and d2.default_status = 1 and d.prediction_date > d2.prediction_date)
order by loan_identifier, prediction_date

Now, I’m trying to delete the rows that were selected, so I adjusted the statement:

delete from pred_loan_defaults  d
where exists (select * from pred_loan_defaults d2 
where d.exec_id = d2.exec_id and d.loan_identifier = d2.loan_identifier 
and d2.default_status = 1 and d.prediction_date > d2.prediction_date);

But I get an error:

SQL Error [1064] [42000]: (conn=6) You have an error in your SQL
syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ‘d

What is wrong with the delete statement?

>Solution :

You can’t use an alias after the table name in a single-table delete.

You need to use JOIN rather than WHERE EXISTS.

delete d
FROM pred_loan_defaults AS d
JOIN prod_loan_defaults AS d2
    ON d.exec_id = d2.exec_id 
        AND d.loan_identifier = d2.loan_identifier 
        AND d.prediction_date > d2.prediction_date
WHERE d2.default_status = 1