MariaDB: Issues with SQL delete using exist clause

Question

I ran this select in MariaDB and it worked as expected, it was just a select with 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 selected rows, so I adapted 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) There is an error in your SQL Syntax; check the manual for your MariaDB server Version using correct syntax near 'd

delete What's wrong with the statement?

Answer 1

When deleting a single table, aliases cannot be used after the table name.

You need to use JOIN instead of 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