ANSI SQL Solution Use group by in a subquery: delete from my_tab where id not in (select min(id) from my_tab group by profile_id, visitor_id); You need some kind of unique identifier(here, I’m using id). MySQL Solution As pointed out by @JamesPoulson, this causes a syntax error in MySQL; the correct solution is (as shown in … Read more
Add Unique Index on your table: ALTER IGNORE TABLE `TableA` ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`); Another way to do this would be: Add primary key in your table then you can easily remove duplicates from your table using the following query: DELETE FROM member WHERE id IN (SELECT * FROM (SELECT id FROM … Read more
The answer in Oracle is: delete from cpi where (countryid, year) in ((‘AD’, 2010), (‘AF’, 2009), (‘AG’, 1992)) It’s fairly standard SQL syntax and I think MySQL is the same.
If you have to select the id: DELETE FROM table WHERE id IN (SELECT id FROM somewhere_else) If you already know them (and they are not in the thousands): DELETE FROM table WHERE id IN (?,?,?,?,?,?,?,?)
Have you tried SET NOCOUNT OFF?
If you use Sql Server delete from Table where id between 79 and 296 Note : the between statement is inclusive, so rows 79 and 296 will also be deleted After your edit : you now clarified that you want : ID (>79 AND < 296) So use this : delete from Table where id … Read more
DELETE FROM on_search WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))
Deleting data from InnoDB is the most expensive operation you can request of it. As you already discovered the query itself is not the problem – most of them will be optimized to the same execution plan anyway. While it may be hard to understand why DELETEs of all cases are the slowest, there is … Read more
If you haven’t made a backup, you are pretty much fudged.
Recently I learned of the following syntax: DELETE (SELECT * FROM productfilters pf INNER JOIN product pr ON pf.productid = pr.id WHERE pf.id >= 200 AND pr.NAME = ‘MARK’) I think it looks much cleaner then other proposed code.