Page 1 of 1

Deleting multiple records with mysql

Posted: Wed Oct 25, 2006 4:19 pm
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?

Posted: Wed Oct 25, 2006 4:24 pm
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.

Posted: Wed Oct 25, 2006 4:25 pm
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

Posted: Wed Oct 25, 2006 4:27 pm
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.

Posted: Wed Oct 25, 2006 4:29 pm
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. :)

Posted: Wed Oct 25, 2006 4:30 pm
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

Posted: Wed Oct 25, 2006 4:31 pm
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

Posted: Wed Oct 25, 2006 4:44 pm
by Luke
thanks fellas... that is PRECISELY what I was looking for... :D