Solved

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

Posted on 2010-11-15
3
316 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
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 250 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now