You can’t specify target table ‘a’ for update in FROM clause意思是:
不能先select出同一表中的某些值,再update这个表(在同一语句中)
解决方案:我们就需要使用子查询,再写一次select查询嵌套筛选条件
这张表中,我们了解到ID=1,3是相同的数据,需要删除其中一条
错误示范:
delete from name1 where id not in (select max(id) from name1 group by stu_id ,score, course)
注意,这个问题只出现于mysql,mssql和Oracle不会出现此问题。
正确示范:
delete from name1 where id not in
(
select a.id from
(
select max(id) id from name1 a where EXISTS
(
select 1 from name1 b group by stu_id ,score, course HAVING count(1)>1
)
group by stu_id ,score, course
) a
)
delete from <table_name> wehere id not in (select max(id) from <table_name> group by <除id外的其他字段>