SQL query to change userid's by one digit.

[mhackl]

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?

[gmerin]

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;

[mhackl]

Thank you very much for your response!

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?

[gmerin]

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/articles/mysql-datadictionary.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 ;) }