Interview Question
0of 0 voteswrite query to delete duplicate rows in MySQL ?
Country: United States
I don't know if there's a super convenient way of doing it, but you could try:
SELECT DISTINCT * INTO #temp FROM YourTable;
TRUNCATE TABLE YourTable;
SELECT * INTO YourTable FROM #temp;
DROP TABLE #temp;
You might want to attempt this in a transaction.
Sorry, didn't see that it was specific to MySQL. But I'm sure something very similar can be done in MySQL. #temp is a temp table.
I would also point out that you *need* to attempt this in an atomic transaction, unless it's not possible that someone else might modify the database concurrently. If someone could be modifying the database concurrently, they could insert data in between the time you do SELECT DISTINCT * INTO #temp FROM YourTable; and
TRUNCATE TABLE YourTable;, and the truncate would lose all of their changes. And then you couldn't roll them back, too.
DISTINCT is not a function. It's a SQL keyword, so it doesn't seem that reasonable to deny me use of this. Can I use GROUP BY, or is that not OK either? If GROUP BY can't be used either, what can? What's the set of allowed operations?
It's sort of arbitrary to deny DISTINCT and allow GROUP BY. You can just change the first line to SELECT * INTO #temp FROM YourTable GROUP BY *
If your dialect of SQL won't allow * for GROUP BY, you'll have to name the columns explicitly. If that has to be dynamic (columns are not known at coding time), you'll have to look at metadata to get that info and then dynamically construct a SQL string for execution.
First, you will need to create a non repeat id for each row.
- Ares.Luo.T September 06, 2012Then use
DELETE FROM foo WHERE id NOT IN (SELECT min(id) FROM foo GROUP BY col1,col2,...)