How to update multiple users, in multiple columns, and in multiple tables in a database at a time

Question

How to update multiple users, in multiple columns, and in multiple tables in a database at a time

CH&HU-1021641

Ten Centuries

Points: 1387
More actions
February 20, 2009 at 12:27 am

#200111

Hi All,
I have a problem with the USERIDs in my tables. Initially there are USERIDs named as 'rex.arnold' in many columns in a single table and with the same userid there multiple tables. we have 800+ tables and 80 columns per table. This userid will be in max of 5 to 11 columns for single table.
Now i want to update many userids in many tables and in multiple columns at a time. is that possible to update userids at a time instead finding / searching the userid for each table and updating manually ? hope it goes days in updating ... 🙁
Example:
USERID: 'rex.arnold' must be changed to 'rexarnold' in multiple tables, in multiple columns.
wherever it looks as 'rex.arnold' must be updated to'rexarnold'
Sample Query : By this we can perform only on a single table (GlobalInbox) and for different columns for single USER. We can't check for all the tables and columns to write a query right ?
QUERY :
update GlobalInbox set senderId='rexarnold', Recipientid='rexarnold' , Requestfor ='rexarnold' , createdby='rexarnold'where senderId='rex.arnold' or Recipientid='rex.arnold' or Requestfor ='rex.arnold' or createdby='rex.arnold'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't cross post. It just wastes peoples time and fragments replies. Was it really necessary to post this four times?

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic661081-146-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

cshekhar (2/20/2009)
Example:
USERID: 'rex.arnold' must be changed to 'rexarnold' in multiple tables, in multiple columns.
wherever it looks as 'rex.arnold' must be updated to'rexarnold'
Sample Query : By this we can perform only on a single table (GlobalInbox) and for different columns for single USER. We can't check for all the tables and columns to write a query right ?
QUERY :
update GlobalInbox set senderId='rexarnold', Recipientid='rexarnold' , Requestfor ='rexarnold' , createdby='rexarnold'where senderId='rex.arnold' or Recipientid='rex.arnold' or Requestfor ='rex.arnold' or createdby='rex.arnold'

This code creates the query u listed above for each tables u wish to update.....It creates the update script for each table..

create table temp1 (sno int identity(1,1),id1 varchar(20),id2 varchar(20))

GO

insert into temp1 values ('abc.username','abc.username')

GO

create table temp2 (sno int identity(1,1),id1 varchar(20),id2 varchar(20))

GO

insert into temp2 values ('abc.username','abc.username')

GO

create table temp3 (sno int identity(1,1),id1 varchar(20),id2 varchar(20))

GO

insert into temp3 values ('abc.username','abc.username')

GO

select identity(int,1,1) AS sno,[name] as tablename

into #GetTables

from sys.objects where create_date>getdate()-1

and type='U'

Declare @count int,@tablename varchar(20),@UpdateQry varchar(2000)

select @count=count(*) from #GetTables

While (@count>0)

BEGIN

select @tablename=tablename from #GetTables where sno=@count

print @tablename

set @UpdateQry='update ' + @tablename + ' set id1=''abcusername'',id2=''abcusername''

where id1=''abc.username'' or id2=''abc.username'''

set @count=@count - 1

--print @Updateqry

exec(@UpdateQry)

END

Regards,
[font="Verdana"]Sqlfrenzy[/font]