vb.org Archive - SQL Query : Mass move specific users based on usernames, not userid

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - SQL Query : Mass move specific users based on usernames, not userid (https://vborg.vbsupport.ru/showthread.php?t=281146)

flyguye

04-05-2012 09:21 AM

SQL Query : Mass move specific users based on usernames, not userid

Hi,

I need to mass move specific users (a couple of hundred of them) based on their usernames, not based on their id, to be deleted at a later time.

Each user is unique, with differing post counts, join dates, etc.

I've tried to mess around with some SQL queries but no luck.

PHP Code:


		
			
UPDATE user 

SET usergroupid = X 

WHERE user = adam john mike etc

I have the usernames on excel so if I could add them into the query either comma separated, or space separated, or line separated would be great! :up:

I've no knowledge on SQL, so I might be shootin' in the dark here! :confused:

Pandemikk

04-05-2012 09:24 AM

Code:

UPDATE user 

SET usergroupid = X 

WHERE username IN('Apostrophe is important', 'etc')

Only problem is you have to make sure the username's are escaped. Feel free to PM me a list of the username's and I'll write up a quick script that'll get the job done.

flyguye

04-05-2012 09:49 AM

Very kind of you sir! However the list isn't finalized yet, will PM you the list once it's finalized!

However, I don't understand what you mean by the usernames being escaped?

--------------- Added [DATE]1333624292[/DATE] at [TIME]1333624292[/TIME] ---------------

Managed to do it!

I used excel to add the 'apostrophes' and appropriate spaces at the end of each username, and pasted it in the following format:

Code:

UPDATE user 

SET usergroupid = X 

WHERE username IN(

'adam', 

'john', 

'mike'

)

Where X is the target usergroupid

That allowed me to paste the all the usernames at one go!

Thanks again! :up: SOLVED!

Pandemikk

04-05-2012 01:17 PM

By escaped, I mean there are certain characters in usernames that would break the query, most notably an apostrophe itself. But seeing as how everything worked out I'm going to assume none of the username's had an apostrophe in them, anyway. ;)

Glad I could be of assistance. :D

All times are GMT. The time now is 04:56 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01005 seconds Memory Usage 1,717KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: