我有张表有3个字段分别为 rowid,id,name
其中rowid是主键,现在有id重复姓名不同的很多数据
我想将id重复的数据留下一条去掉重复 实现如下
delete from test1
where id in (select id from test1 group by id having count(id) > 1)
and rowid not in (select min(rowid) from test1 group by id having count(id )>1)
但是执行报错:
You can't specify target table 'test1' for update in FROM clause
怎么将语句格式化下才能正确执行呢
其中rowid是主键,现在有id重复姓名不同的很多数据
我想将id重复的数据留下一条去掉重复 实现如下
delete from test1
where id in (select id from test1 group by id having count(id) > 1)
and rowid not in (select min(rowid) from test1 group by id having count(id )>1)
但是执行报错:
You can't specify target table 'test1' for update in FROM clause
怎么将语句格式化下才能正确执行呢
(select id from test1 group by id having count(id) > 1) b on a.id=b.id
inner join
(select min(rowid) from test1 group by id having count(id )>1) c
on a.rowid=c.rowid
where rowid not in (select min(rowid) from test1 group by id having count(id )>1)
select max(rowid),id,max(name) from test1 group by id 这样行吗?