Updating Options en masse

[Colin F]

Show Signatures
On: UPDATE user SET options=options + 1 WHERE NOT(options & 1);
Off: UPDATE user SET options=options - 1 WHERE options & 1;

Show Avatars
On: UPDATE user SET options=options + 2 WHERE NOT(options & 2);
Off: UPDATE user SET options=options - 2 WHERE options & 2;

Show Images
On: UPDATE user SET options=options + 4 WHERE NOT(options & 4);
Off: UPDATE user SET options=options - 4 WHERE options & 4;

COPPA User
Yes: UPDATE user SET options=options + 8 WHERE NOT(options & 8);
No: UPDATE user SET options=options - 8 WHERE options & 8;

Receive Admin Emails
On: UPDATE user SET options=options + 16 WHERE NOT(options & 16);
Off: UPDATE user SET options=options - 16 WHERE options & 16;

vCard Download
On: UPDATE user SET options=options + 32 WHERE NOT(options & 32);
Off: UPDATE user SET options=options - 32 WHERE options & 32;

DST Auto-Correct
On: UPDATE user SET options=options + 64 WHERE NOT(options & 64);
Off: UPDATE user SET options=options - 64 WHERE options & 64;

DST On/Off
On: UPDATE user SET options=options + 128 WHERE NOT(options & 128);
Off: UPDATE user SET options=options - 128 WHERE options & 128;

Show Email Address
On: UPDATE user SET options=options + 256 WHERE NOT(options & 256);
Off: UPDATE user SET options=options - 256 WHERE options & 256;

Invisible
On: UPDATE user SET options=options + 512 WHERE NOT(options & 512);
Off: UPDATE user SET options=options - 512 WHERE options & 512;

Show Reputation
On: UPDATE user SET options=options + 1024 WHERE NOT(options & 1024);
Off: UPDATE user SET options=options - 1024 WHERE options & 1024;

Receive PMs
On: UPDATE user SET options=options + 2048 WHERE NOT(options & 2048);
Off: UPDATE user SET options=options - 2048 WHERE options & 2048;

Get email for new PMs
On: UPDATE user SET options=options + 4096 WHERE NOT(options & 4096);
Off: UPDATE user SET options=options - 4096 WHERE options & 4096;

Show New Private Message Notification Pop-up
On: UPDATE user SET pmpopup = 1;
Off: UPDATE user SET pmpopup = 0;

Post Order
Newest first: UPDATE user SET options=options + 32768 WHERE NOT(options & 32768);
Oldest first: UPDATE user SET options=options - 32768 WHERE options & 32768;

Receive Private Messages only from Buddies
On: UPDATE user SET options=options + 131072 WHERE NOT(options & 131072);
Off: UPDATE user SET options=options - 131072 WHERE options & 131072;

[Colin F]

Additional queries that aren't covered by the options bitfield:

Thread Display Mode
Linear: UPDATE user SET threadedmode=0;
Threaded: UPDATE user SET threadedmode=1;
Hybrid: UPDATE user SET threadedmode=2;

Popup on new PM
On: UPDATE user SET pmpopup=1;
Off: UPDATE user SET pmpopup=0;

Default daysprune setting
UPDATE user SET daysprune=X;
where X is the number of days.

Language
UPDATE user SET languageid=X;
where X is the ID of the language (you can get this from the admin CP).

Style
UPDATE user SET styleid=X;
where X is the ID of the style (you can get this from the admin CP).

First day of the week
UPDATE user SET startofweek=X;
where X is a number 1 through 7 indicating the first day of the week (with 1 being Sunday, 2 being Monday, etc.).

Message Editor Interface
Do Not Show Editor Toolbar: UPDATE user SET showvbcode=0;
Show Standard Editor Toolbar: UPDATE user SET showvbcode=1;
Show Enhanced (WYSIWYG) Editor Toolbar: UPDATE user SET showvbcode=2;

Thread subscription/email notification defaults
Do not subscribe: UPDATE user SET autosubscribe = -1;
Subscribe without notification: UPDATE user SET autosubscribe = 0;
Instant email: UPDATE user SET autosubscribe = 1;
Daily email: UPDATE user SET autosubscribe = 2;
Weekly email: UPDATE user SET autosubscribe = 3;

Show Birthday
Hide Age & Date of Birth: UPDATE user SET showbirthday = 0;
Display Age: UPDATE user SET showbirthday = 1;
Display Age & Date of Birth: UPDATE user SET showbirthday = 2;

Timezoneoffset
UPDATE user SET timezoneoffset = X;
where X is the GMT value (0, 2, -5, ...)

Allow use of admin-set custom avatar
On: UPDATE user SET adminoptions=adminoptions + 1 WHERE NOT(adminoptions & 1);
Off: UPDATE user SET adminoptions=adminoptions - 1 WHERE adminoptions & 1;

Allow use of admin-set custom profile picture
On: UPDATE user SET adminoptions=adminoptions + 2 WHERE NOT(adminoptions & 2);
Off: UPDATE user SET adminoptions=adminoptions - 2 WHERE adminoptions & 2;

[Colin F]

Make all forums active/inactive:
Active: UPDATE forum SET options=options + 1 WHERE NOT(options & 1);
Inactive: UPDATE forum SET options=options - 1 WHERE (options & 1);

Open/Close all forums for new posts:
Open: UPDATE forum SET options=options + 2 WHERE NOT(options & 2);
Close: UPDATE forum SET options=options - 2 WHERE (options & 2);

Set all forums to act as categories/forums:
Act as forums: UPDATE forum SET options=options + 4 WHERE NOT(options & 4);
Act as categories:UPDATE forum SET options=options - 4 WHERE (options & 4);

Turn on/off Post Moderation for all forums:
On: UPDATE forum SET options=options + 8 WHERE NOT(options & 8);
Off: UPDATE forum SET options=options - 8 WHERE (options & 8);

Turn on/off Thread Moderation for all forums:
On: UPDATE forum SET options=options + 16 WHERE NOT(options & 16);
Off: UPDATE forum SET options=options - 16 WHERE (options & 16);

Turn on/off Attachment Moderation for all forums:
On: UPDATE forum SET options=options + 32 WHERE NOT(options & 32);
Off: UPDATE forum SET options=options - 32 WHERE (options & 32);

Turn on/off BBCode for all forums:
On: UPDATE forum SET options=options + 64 WHERE NOT(options & 64);
Off: UPDATE forum SET options=options - 64 WHERE (options & 64);

Turn on/off Images for all forums:
On: UPDATE forum SET options=options + 128 WHERE NOT(options & 128);
Off: UPDATE forum SET options=options - 128 WHERE (options & 128);

Turn on/off HTML for all forums:
On: UPDATE forum SET options=options + 256 WHERE NOT(options & 256);
Off: UPDATE forum SET options=options - 256 WHERE (options & 256);

Turn on/off Smilies for all forums:
On: UPDATE forum SET options=options + 512 WHERE NOT(options & 512);
Off: UPDATE forum SET options=options - 512 WHERE (options & 512);

Turn on/off Post Icons for all forums:
On: UPDATE forum SET options=options + 1024 WHERE NOT(options & 1024);
Off: UPDATE forum SET options=options - 1024 WHERE (options & 1024);

Turn on/off Thread Rating for all forums:
On: UPDATE forum SET options=options + 2048 WHERE NOT(options & 2048);
Off: UPDATE forum SET options=options - 2048 WHERE (options & 2048);

Turn on/off Post Counting for all forums:
On: UPDATE forum SET options=options + 4096 WHERE NOT(options & 4096);
Off: UPDATE forum SET options=options - 4096 WHERE (options & 4096);

Set 'Can have Password' on/off for all forums:
On: UPDATE forum SET options=options + 8192 WHERE NOT(options & 8192);
Off: UPDATE forum SET options=options - 8192 WHERE (options & 8192);

Turn on/off Search Indexing for all forums:
On: UPDATE forum SET options=options + 16384 WHERE NOT(options & 16384);
Off: UPDATE forum SET options=options - 16384 WHERE (options & 16384);

Turn on/off Style Overriding for all forums:
On: UPDATE forum SET options=options + 32768 WHERE NOT(options & 32768);
Off: UPDATE forum SET options=options - 32768 WHERE (options & 32768);
If this is turned On, then the styleid should be updated for all the forums as well, by running this query, where X is the styleid number:UPDATE forum SET styleid = X;

Turn on/off 'Show on ForumJump Menu' for all forums:
On: UPDATE forum SET options=options + 65536 WHERE NOT(options & 65536);
Off: UPDATE forum SET options=options - 65536 WHERE (options & 65536);

Change 'Default Sort Field' for all forums:
Thread Title: UPDATE forum SET defaultsortfield = 'title';
Last Post Time: UPDATE forum SET defaultsortfield = 'lastpost';
Thread Start Time: UPDATE forum SET defaultsortfield = 'dateline';
Number of Replies: UPDATE forum SET defaultsortfield = 'replycount';
Number of Views: UPDATE forum SET defaultsortfield = 'viewcount';
Thread Starter: UPDATE forum SET defaultsortfield = 'postusername';
Thread Rating: UPDATE forum SET defaultsortfield = 'voteavg';

Change 'Default Sort Order' for all forums:
Descending: UPDATE forum SET defaultsortorder = 'desc';
Ascending: UPDATE forum SET defaultsortorder = 'asc';