DELETE ALL EXCEPT x

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

DELETE ALL EXCEPT x

Post by Luke »

How would I (and this is just hypothetical) delete all from database where user_id != 1, 2, 3, 5, or 6
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

delete from `users` where `user_id` != 1 and `user_id` != 2 and `user_id` != 3 and `user_id` != 4 and `user_id` != 5 and `user_id` != 6;
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Code: Select all

DELETE FROM `users` WHERE `user_id` NOT IN (1, 2, 3, 5, 6 )
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I like that method better.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

how do you make that SQL box like astions did? I tried [sql] and it didn't work.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

The Ninja Space Goat wrote:how do you make that SQL box like astions did? I tried [sql] and it didn't work.

[ syntax="sql" ]
delete from `users` where `user_id` != 1 and `user_id` != 2 and `user_id` != 3 and `user_id` != 4 and `user_id` != 5 and `user_id` != 6;
[ /syntax ]

Remove the space before/after the opening and closing [] of course.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Code: Select all

 neat! 
Post Reply