January 9, 2009 at 9:28 am
There are 10 similarly named tables (in the same database):
table01
table02
table03
table04
table05
table06
table07
table08
table09
table10
In each table, the same fieldname needs to be updated.
What is the most efficient way to loop through the tables?
Thanks for any input.
January 9, 2009 at 9:30 am
Why not just do:
update dbo.Table01
set SomeField = SomeValue
where Whatever = Something
update dbo.Table02
...
update dbo.Table03
...
....
Why "loop through", instead of just writing the code?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 9:33 am
there are many tables. not only 10. I was hoping there was a more efficient way.
January 9, 2009 at 10:02 am
Do you mean the actual column name needs to be changed or the data in the column needs to be updated?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 9, 2009 at 10:08 am
Sorry, a string of data, in the column, of the selected tables needs to be modified.
January 9, 2009 at 10:29 am
I guess you could create a view which is the union of all your tables, and then update the view.
Having many similarly named tables is usually an indication that a redesign would be beneficial. How come you have them?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
January 9, 2009 at 11:24 am
Well you could use the undocumentes sp_MSForEachTable procedure to loop through and run the update. Something like this:
Exec sp_MSforeachtable @command1 = 'Update ? Set column_Name = value' , @WhereAnd = ' and O.name like ''tablename%'''
Here is a link to a good article about the procedure: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 11, 2009 at 2:25 pm
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + 'UPDATE ' + name + ' set somecolumn = somecolumn + ''hello''; '
from sysobjects
where type = 'U'
and name like 'table%'
exec (@sql)
You will have to change the somecolumn bit, and the name like 'table%' bit to suit your needs - you weren't very specific about them so you got an inspecific answer.
Watch out for the upper size limit on the varchar if there are *a lot* of tables.
edit -> formatting.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 12, 2009 at 7:07 am
RyanRandall (1/9/2009)
I guess you could create a view which is the union of all your tables, and then update the view.Having many similarly named tables is usually an indication that a redesign would be beneficial. How come you have them?
Normally you can't update a view that has a union operator in it, unless you follow very specific rules about partitioned tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 8:08 am
I echo the concern for your database design. Why can't they all be in one table with a field the stores the 1, 2, etc.
Or maybe the field that needs to be updated should actually be in a lookup table so it is only changed in one place and make it easier to change in the future.
Steve
January 12, 2009 at 8:29 am
GSquared (1/12/2009)
Normally you can't update a view that has a union operator in it, unless you follow very specific rules about partitioned tables.
Good catch, GSquared - I was careless with my lack of information here (I'm a bit rusty right now :)). You would need an instead of trigger or check constraints to use my suggestion (and even then there are limitations). Here are some examples...
--drop test tables
drop table dbo.test01
drop table dbo.test02
drop view dbo.testView
--/
--existing structure and data (for example)
create table dbo.test01 (a int primary key, b int)
create table dbo.test02 (a int primary key, b int)
insert dbo.test01 select 11, 12
insert dbo.test01 select 13, 14
insert dbo.test02 select 25, 26
go
--/
--create view
create view dbo.testView as
select * from dbo.test01
union all
select * from dbo.test02
go
--/
--------Option 1 - Using instead of trigger
--create instead of trigger on view
create trigger dbo.testview_updatetrigger on dbo.testview
instead of update
as
if @@rowcount = 0
return
update t set b = u.b from dbo.test01 t inner join inserted u on t.a = u.a
update t set b = u.b from dbo.test02 t inner join inserted u on t.a = u.a
go
--/
--show view before update
select * from dbo.testView
--/
--update view
update dbo.testView set b = 10
--/
--show view after update
select * from dbo.testView
--/
go
--------/
--------Option 2 - Using check constraints
--add check constraints
alter table dbo.test01 add check (a between 10 and 19)
alter table dbo.test02 add check (a between 20 and 29)
go
--/
--show view before update
select * from dbo.testView
--/
--update view
update dbo.testView set b = 999
--/
--show view after update
select * from dbo.testView
--/
go
--------/
--results
a b
----------- -----------
11 12
13 14
25 26
a b
----------- -----------
11 10
13 10
25 10
a b
----------- -----------
11 10
13 10
25 10
a b
----------- -----------
11 999
13 999
25 999
--/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
January 13, 2009 at 7:37 am
I still don't get why a multi-table script is such a big deal.
Let's say the field name is "MyField".
You run:
select 'Update dbo.' + name + ' set MyField = 5;'
from sys.tables
where object_id in
(select object_id
from sys.columns
where name = 'MyField')
You copy and paste the results into the query editor in Management Studio, and you run it.
What am I missing that makes that inefficient?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 9:07 am
...or just dump it into some dynamic SQL. Or, if the tables change every month, have some dynamic SQL build a proc for you. You can even build a temporary stored procedure very much like you can build a temporary table.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2009 at 4:39 pm
Jeff Moden (1/13/2009)
...or just dump it into some dynamic SQL.
That was pretty much my take on it 🙂
Code above - OP seems to have gone AWOL though!
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 13, 2009 at 7:06 pm
Felix! Are you all set?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply