SQL delete with exists is not working in MariaDB-CodePudding

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?

CodePudding user response：

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