SQL语法类问题

 案例一   delete from  table  table 不能使用别名

delete from loan_contract_upload_record record where record.loan_id in (
select contract.LOAN_ID from loan_contract contract where contract.loan_id in (select info.id from loan_info info where info.LOAN_STATUS = '002001005' and info.LENDING_TAG = 'ASD'))

 

案例二 delete 不能使用join 

delete loan_contract from loan_contract LEFT JOIN loan_info a on a.ID = loan_contract.LOAN_ID WHERE a.LENDING_TAG = 'XIAOYING'
AND loan_contract.CREATE_TIME >= '2018-12-25 12:00:00'; 

 

案例三  You can't specify target table 'ssq_contract_record' for update in FROM clause

[Err] 1093 - You can't specify target table 'ssq_contract_record' for update in FROM clause

这个错误的意思是不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。 

解决办法：

嵌套一个子查询

 

案例四 update子查询改写为left join