sql: remove dupliacte rows except one with limit

Question

This is my sql query for remove duplicate rows except one with limit

DELETE n1 FROM v_news n1, v_news n2 WHERE n1.`id` > n2.`id` AND n1.`url` = n2.`url` ORDER BY n2.`id` LIMIT 100

But i get the error like this:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY n2.`id` LIMIT 100' at line 1

Where is my wrong?

Thanks in advance.

Can remove "order by". But there must be a limit. Because the number of rows is very high. — Jake, Sep 15 '17 at 09:45
What you want to do is not possible using the syntax you want. I would suggest that you ask *another* question with sample data and desired results. — Gordon Linoff, Sep 15 '17 at 09:48
FYI: I'm not familiar enough with MySQL to answer; but in SQL Server you could limit the number of rows deleted this like so: https://stackoverflow.com/a/8956164/361842. Providing in case that gives any hints. — JohnLBevan, Sep 15 '17 at 09:49
@yılmaz Actually, after added the LIMIT to query this error is shown. — Jake, Sep 15 '17 at 09:55
@Jake can you provide sample data and the data you want after running code? — yılmaz, Sep 15 '17 at 09:59

score 0 · Answer 1 · answered Sep 15 '17 at 09:46

0

For delete query no need to include order by and limit. So just remove those two and try like below,

DELETE n1 FROM v_news n1, v_news n2 WHERE n1.`id` > n2.`id` AND n1.`url` = n2.`url`

answered Sep 15 '17 at 09:46

KMS

566
4
16

I think the point is @Jake wants to only delete up to 100 rows (not sure why, but presumably there's an underlying logic to this). – JohnLBevan Sep 15 '17 at 09:47
Thanks, I used the limit because the number of rows in my table is millions and the server is unable to complete the request. – Jake Sep 15 '17 at 09:52
so that u can use delete command within `for` loop. It may will work – KMS Sep 15 '17 at 09:55

score 0 · Answer 2 · answered Sep 15 '17 at 09:47

As the documentation explains:

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a multiple-table DELETE.

In other words, your DELETE references two tables (well, the same table twice), so it is considered a multiple-table delete. ORDER BY and LIMIT are not allowed.

Note: You should use JOIN rather than a comma for specifying the tables.

score 0 · Answer 3 · answered Sep 15 '17 at 10:28

This approach may work:

--use a temp table record the ids of the records you wish to delete
create temporary table if not exists newsIdsToDelete 
(
    Id bigint
    , PRIMARY KEY (id)
) ENGINE=MEMORY;

--populate that table with the IDs from your original query
select n1.`Id` 
into newsIdsToDelete
from v_news n1
inner join v_news n2 
on n2.`id` < n1.`id` 
and n2.`url` = n1.`url` 
order by n2.`id` 
limit 100;

--delete those records where the id's in the list of ids to be deleted
delete v_news 
where `Id` in (
    select `Id` 
    from newsIdsToDelete
);

--clean up the temporary table
drop temporary table if exists newsIdsToDelete;

(I don't have MySql, and my knowledge of that DB is pretty ropey, so please test this in a safe place before using).

sql: remove dupliacte rows except one with limit

3 Answers3