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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-08 : 09:46:38
|
| Sergio writes "I am running SQL Server 2000 on a Windows 2000 server.I need to update the "supplier" field in 15 tables in my database without having to manually change the parameters for every editie. M687 must be changed to M888 F086 must be changed to F231 D210 must be changed to S088 M709 must be changed to M888 ... ... ... H009 must be changed to H078If I use a simple sp like this:UPDATE scheme.planalmSET scheme.planalm.supplier = 'M888' WHERE scheme.planalm.supplier = 'M687'I would have to change the correct and incorrect supplier code for each entry to be edited.Is there any way that this can be done all in one go as I have between 250 and 300 supplier codes to change in each of the 15 tables.Many thanksSergio Scopazzi" |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2002-05-08 : 10:05:02
|
| If they're all named the same, you can use the INFORMATION_SCHEMA view COLUMNS to list all the tables where the column name exists. You can then recurse through these updating the values in each.Be careful....Tim |
 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-05-08 : 10:10:50
|
| Assuming you have both the old and new codes, in a table.create table #tblMapping (oldcode char(5), newcode char(5))Try this :select 'UPDATE scheme.planalm SET scheme.planalm.supplier = ' +plus oldcode +plus'WHERE scheme.planalm.supplier = ' +plus newcode from #tblMappingSorry about the 'plus' - can't get the symbol to display???This will generate the UPDATE statements.You can then replace the table names on each run. |
|
|
|
|
|
|
Updating records