Deleting multiple records with mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Deleting multiple records with mysql

Post by Luke »

I know you can delete records from a database like this:

Code: Select all

DELETE FROM table WHERE id = 1
And I've always just executed multiple queries that looked like this if I wanted to delete multiple records... because that's the way phpmyadmin does it... is there a way to delete multiple records with one query?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

just indiate what you want deleted within your where clause:

ie:

Code: Select all

DELETE FROM `myTable` WHERE `name` LIKE 'burr%'
would delete:
burrito
burriskimo
burr-Im-cold

etc.
kyzercube
Forum Newbie
Posts: 5
Joined: Wed Oct 25, 2006 3:19 pm

Post by kyzercube »

Well, if it's not like a million id's, you can do this:

"DELETE FROM TABLE WHERE id = 1 AND id=2 AND id=3"

just use the AND argument between the id numbers
Last edited by kyzercube on Wed Oct 25, 2006 4:29 pm, edited 1 time in total.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

OK... I was hoping to avoid this huge query... but I guess it doesn't really matter...

Code: Select all

DELETE FROM table WHERE id = 1
OR id = 2
OR id = 3
OR id = 4
OR id = 5
OR id = 6
OR id = 23
OR id = 56
OR id = 43
OR id = 9
OR id = 11
OR id = 38
There's no way for a record to have id of 1 AND an id of 2 and 3 buddy.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The Ninja Space Goat wrote:OK... I was hoping to avoid this huge query... but I guess it doesn't really matter...

Code: Select all

DELETE FROM table WHERE id = 1
OR id = 2
OR id = 3
OR id = 4
OR id = 5
OR id = 6
OR id = 23
OR id = 56
OR id = 43
OR id = 9
OR id = 11
OR id = 38
There's no way for a record to have id of 1 AND an id of 2 and 3 buddy.
eeeww

Code: Select all

DELETE FROM table WHERE field IN('1', '2', '3')
may be a bit smoother. :)
kyzercube
Forum Newbie
Posts: 5
Joined: Wed Oct 25, 2006 3:19 pm

Post by kyzercube »

I maybe mistaken, but I don't think quotes are required for integers

but that script would definately be easier to deal with
Last edited by kyzercube on Wed Oct 25, 2006 4:31 pm, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

The Ninja Space Goat wrote:OK... I was hoping to avoid this huge query... but I guess it doesn't really matter...

Code: Select all

DELETE FROM table WHERE id = 1
OR id = 2
OR id = 3
OR id = 4
OR id = 5
OR id = 6
OR id = 23
OR id = 56
OR id = 43
OR id = 9
OR id = 11
OR id = 38
There's no way for a record to have id of 1 AND an id of 2 and 3 buddy.
use IN()

ie:

Code: Select all

DELETE FROM `myTable` WHERE `id` IN ('1','2','3','4','...')
edit: wtf... two posts before I hit the submit button? you guys are too quick
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

thanks fellas... that is PRECISELY what I was looking for... :D
Post Reply