Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Luke
The Ninja Space Mod
Posts: 6424 Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA
Post
by Luke » Wed Oct 25, 2006 4:19 pm
I know you can delete records from a database like this:
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?
Burrito
Spockulator
Posts: 4715 Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah
Post
by Burrito » Wed Oct 25, 2006 4:24 pm
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 » Wed Oct 25, 2006 4:25 pm
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.
Luke
The Ninja Space Mod
Posts: 6424 Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA
Post
by Luke » Wed Oct 25, 2006 4:27 pm
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Oct 25, 2006 4:29 pm
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 » Wed Oct 25, 2006 4:30 pm
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.
Burrito
Spockulator
Posts: 4715 Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah
Post
by Burrito » Wed Oct 25, 2006 4:31 pm
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
Luke
The Ninja Space Mod
Posts: 6424 Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA
Post
by Luke » Wed Oct 25, 2006 4:44 pm
thanks fellas... that is PRECISELY what I was looking for...