February 20, 2009 at 12:22 am
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 Qeury : 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 ?
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'
Help me out........ as am blocked in searching...!!! 🙁
Thanks in advance.
Maddy.
February 20, 2009 at 3:19 am
Clearly define your rules, if they are systemic you can write a query which output would be the code you have to execute.
One of the beauties of RDBMS is that everything the database knows is stored in tables you can query, SQL Server already knows the names of your tables and each column on them. Be creative.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 20, 2009 at 6:21 am
You can only update one table at a time, yes, but you can wrap all the updates within a transaction. That will make them all "occur" at the same time.
Probably what you're hoping for though is a mechanism to hit all tables at once in a single statement. You can't. You'll have to work through the correct set of tables and, if there are referential constraints on the data, the correct order, or you'll need to drop the constraints while you do the update. Data cleanup is very tedious and time consuming work. It's not hard. You just have to be careful and take the time. Be sure to test thoroughly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply