?
Solved

How can you delete multiple rows with a single XSQL statement?

Posted on 2010-11-15
3
Medium Priority
?
360 Views
Last Modified: 2012-06-21
I am needing to delete 500 discrete records from a table which all have unique ids but this is taking a LONG time.  I'm wanting to optimize the process by doing it with a single statement.  But what I'm trying says "zero rows deleted"

DELETE FROM `mytable` WHERE (`id` = '123456') OR (`id`  = '456789');

Any leads?  What's the most efficient way to delete a lot of data from a table?
0
Comment
Question by:kirin0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Expert Comment

by:sandipshah
ID: 34141765
create a temp table with all the ids you want to delete with an index on that field

then delete like this

delete a.* from table1 a inner join temp table b on a.id = b.id

Ss

0
 

Accepted Solution

by:
thomsonalex earned 1000 total points
ID: 34141777
Try this out

DELETE FROM `mytable` WHERE  ID IN ('123456', '456789'')

0
 

Author Closing Comment

by:kirin0
ID: 34142262
Perfect... thanks.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question