Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 30 Oct 2009, 19:56
mhackl mhackl is offline
 
Join Date: Sep 2006
SQL query to change userid's by one digit.

Since I migrated to a new installation of vbulletin userid's are off by one digit due to the new installs "admin"account. What query would I run to change every userid by one digit?
__________________
vBulletin® Version 4.0.1 | PHP version 5.2.2 | Mysql version 5.0.24a | Netfirms "Enterprise One" Hosting
Reply With Quote
  #2  
Old 31 Oct 2009, 19:29
gmerin gmerin is offline
 
Join Date: Dec 2008
this will change the userid, but i'm pretty certain that's not what you really want, since the rows in the other tables tend to relate the users' data by the userid so changing it in just one table will just screw up your board. i'm figuring what you really want to do is to increment the userid in all the impacted tables, which is why there are vb migration programs/scripts for doing these sort of tasks.

but in case you want to see the sql for just what you asked...

--assuming you have sql access to your mysql instance, first, this will show you what the update sql statement will do without making the change:

select a.userid, a.userid+1 from user a
order by a.userid;

--this will make the change (a.userid+x increments; a.userid-x decrements):

update user a
set a.userid = a.userid+1 ;

--if you want to limit or qualify the changes:

update user a
set a.userid = a.userid+1
where <fieldname> = <some appropriate qualifier value> ;

--for example, modify every userid except userid=1:

update user a
set a.userid = a.userid+1
where a.userid > 1;

--always back up the table first:

create table user_bak as
select a.* from user a;

--if you make a mistake and need to restore the original table:

truncate table user;

insert into user
select * from user_bak;

Last edited by gmerin; 31 Oct 2009 at 19:54.
Reply With Quote
  #3  
Old 05 Nov 2009, 06:02
mhackl mhackl is offline
 
Join Date: Sep 2006
Thank you very much for your response!

Originally Posted by gmerin View Post
which is why there are vb migration programs/scripts for doing these sort of tasks.
Are you speaking of impex? If so, this problem is a result of impex. if not, what scripts are you speaking of?
__________________
vBulletin® Version 4.0.1 | PHP version 5.2.2 | Mysql version 5.0.24a | Netfirms "Enterprise One" Hosting
Reply With Quote
  #4  
Old 07 Nov 2009, 19:49
gmerin gmerin is offline
 
Join Date: Dec 2008
ok, since you have mysql v5.0.2+ you can query the information_schema. how to do this would involve writing a book, luckily someone already has:http://dev.mysql.com/tech-resources/...dictionary.pdf

what you want to do is join information_schema.tables to information_schema.columns, select those tables in the vbulletin schema that have the userid column, then update userid from each of those tables with the modified userid from the table user as you loop thru the user table modifying the userids in it.

in other dbms (like oracle & sql server) i would do this with multiple cursors. i'll see what i can come up with for mysql later this week. in php you would do this with the mysql_fetch_row command {but doing it in sql is more challenging and therefore, more fun }
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 11:46.

Layout Options | Width: Wide Color: