我有一个位置mysql db包含3列
Country State City
US California Los Angeles
AU Queensland Brisbane
AU Victoria Southbank
AU Queensland Brisbane如何删除mysql查询中重复的行?
DELETE FROM location WHERE country IN (
SELECT * FROM table_name
GROUP BY country, state, city
HAVING COUNT(*) >
);我试过这个查询,但没有成功
发布于 2013-08-17 11:31:41
您的查询有许多问题:
DELETE FROM location
where country in (SELECT *
FROM table_name
GROUP BY country, state, city
having COUNT(*)>);例如:(1)从子查询中返回多个列,但只返回一列。(2)比较后你没有号码。(3)指两个不同的表格。
按照数据的结构方式(行中没有唯一的id ),要确定要删除哪些行是很困难的。我建议您使用truncate方法代替。然后学习在每个表中放置一个自动递增的id:
create temporary table tmpLocation as
select country, state, city from location;
truncate table location;
insert into location(country, state, city)
select distinct country, state, city
from tmpLocation;
drop table tmpLocation;在有大量删除的大型表上,这也是更有效的。
发布于 2013-08-17 11:31:59
选择不同的行并将它们放在一个新表中:
CREATE TEMPORARY TABLE location_temp AS SELECT DISTINCT * FROM location截断表:
DELETE FROM location;使用不同的值从表中填充
INSERT INTO location(Country, State, City) SELECT Country, State, City FROM location_temp;发布于 2013-08-17 11:29:08
你忘了提到count(*)>1
DELETE FROM location
where country in (
SELECT *
FROM table_name
GROUP BY country, state, city
having COUNT(*)>1);只供您参考。您也可以使用ALTER IGNORE TABLE删除副本。
就像这样:-
ALTER IGNORE TABLE location ADD UNIQUE INDEX (COLUMN1, COLUMN2)https://stackoverflow.com/questions/18288245
复制相似问题