Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shekar811
Starting Member
1 Post |
 Posted - 2009-02-23 : 23:34:27
|
| HOW TO UPDATE MULTIPLE COLUMNS AND MULTIPLE TABLES IN SQL SERVER 2005 DATABASE :we have 1550 tables in a database, we have different columns that have same 'userid' as 'ca.shekhar' in multiple columns. Now we need to update all the existing fields, in multiple tables and in multiple columns at a time. Can we have a script for this as CURSORS ??? or SP ??? EX: Table1 : col1 col2 col3 col4 col5 ca.shekhar ca.shekhar rt tr ty Table2 : col1 col2 col3 col4 col5 sd fg ca.shekhar ca.shekhar jk Table3 : col1 col2 col3 col4 col5 col6 col7 ca.shekhar ca.shekhar gh jk lm ca.shekhar ca.shekhar so and so............... Here we need to change the fields of naming like 'ca.shekhar' to ' cashekhar ' in all the tables and the columns where 'ca.shekhar' will be in 1550 tables As : USERID:'ca.shekhar' must be changed to 'cashekhar' in multiple tables, in multiple columns. wherever it looks as 'ca.shekhar' must be updated to 'cashekhar' Sample Qeury : By this we can perform only on a single table (GlobalInbox) and for different columns. We can't check for all the tables and columns to write a query right ? update GlobalInbox set senderId='cashekhar' , Recipientid='cashekhar' , Requestfor ='cashekhar' , createdby='cashekhar' where senderId='ca.shekhar' or Recipientid='ca.shekhar' or Requestfor ='ca.shekhar' or createdby='ca.shekhar' Thanks in advance...- Johhny |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-24 : 07:15:38
|
| if you had a normalised database you wouldn't be in this mess. you would only have to update 1 row in 1 table. is it too late to start again and do things properly?Normalisation:http://en.wikipedia.org/wiki/Database_normalizationTo answer your question. Yes it is possible. You will have to use dynamic sql (looking up information_schema.columns) and cursor through each table and column name and do a string replace.Try this to get started:http://www.sommarskog.se/dynamic_sql.htmlyou'll have to restrict the columns you want to change to string columns.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 09:27:07
|
| you can use the below script to get details of tables where a particular value is found. then iterate through them and apply dynamic sql for the update |
|
|
|
|
|
|
URGENT PLS-How to Update multiple column values