Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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 …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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