November 27, 2009 at 1:07 pm
So I'm looking for a way to update a variable number of tables with a single update command.
I can do it via cursor, but I have a natural emnity towards those dastardly devils and was looking to see if there's a better way. The sample below doesn't work, but it's where I'm stumped.
Declare @tablename varchar(255)
declare @execstring varchar(255)
declare @0 varchar(1)
select @tablename = (select Name from Sysobjects where Name like 'queue%' and type = 'U')
set @0=0
set @execstring = 'Update ' + @tablename +
'set xcheckout = ' + @0
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any suggestions? I'd really, really hate to use that bloody cursor!
-Matt
November 27, 2009 at 1:18 pm
MatthewA.Herold (11/27/2009)
select @tablename = (select Name from Sysobjects where Name like 'queue%' and type = 'U')
That select must return only one value. You're assigning the result to a single string variable. If you change it as follows, then it won't throw an error, but the variable will still have only one table name in it.
select @tablename = Name from Sysobjects where Name like 'queue%' and type = 'U'
Updates can only target single tables. A single update statement updates a single table. If you're updating multiple tables, you need multiple update statements. There are many ways to generate them, cursors, while loops or building up strings as part of a query.
p.s. You're on SQL 2005, sysobjects is deprecated. Use sys.objects or sys.tables
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
November 27, 2009 at 10:23 pm
Your Statement Is
select @tablename = (select Name from Sysobjects where Name like 'queue%' and type = 'U')
set @0=0
set @execstring = 'Update ' + @tablename +
'set xcheckout = ' + @0
You must use top 1 in sub query then the error message is remove.
but you can update only one table at a time.
Your Idea is right you can use cursor here.
but note that u can also use a updatable view for update multiple table in one statement.
November 28, 2009 at 12:21 am
vbprogrammer1986 (11/27/2009)
but note that u can also use a updatable view for update multiple table in one statement.
But the update must still target a single table, ie with an updateable view, all the columns updated be part of a single table. Otherwise there has to be an instead of trigger.
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
November 28, 2009 at 12:59 am
Yes
You are right.
thanks for update me.
November 28, 2009 at 2:15 am
Yeah.
Already made the cursor a while ago, I was just looking for a way to avoid using it.
Guess not 🙁
I hate cursors.
November 30, 2009 at 2:41 am
Is is sql server 2005? if yes this is how you can achieve it without using cursors, while loop...
declare @execstring varchar(max)
declare @0 varchar(1)
set @0=0
select @execstring = isnull(@execstring,'') + ' update ' + name + ' set ' + 'xcheckout = ' + @0
from sys.tables where Name like 'queue%' and type = 'U'
exec(@execstring)
Note: I have made few changes to you code as below
A. changed the datatype of execstring to varchar(max)
B. replaced sysobjects with sys.tables
December 1, 2009 at 7:39 am
MatthewA.Herold (11/28/2009)
Yeah.Already made the cursor a while ago, I was just looking for a way to avoid using it.
Guess not 🙁
I hate cursors.
This is a perfect scenario where a cursor is indeed the appropriate mechanism to use. Use the right tool for the job, even if everyone says you should never use it. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2009 at 2:35 pm
MatthewA.Herold (11/28/2009)
Yeah.Already made the cursor a while ago, I was just looking for a way to avoid using it.
Guess not 🙁
I hate cursors.
The reason to avoid cursors is because they are procedural and in SQL, it is much preferable to use set-based operations. In this case, however, SQL Server has no provision for updating a set of tables -- you have to do them serially, and a cursor is probably as good a way to do that as any (though, the while loop is easier and probably faster since it is much less general than a cursor). The updates are still set-based but you can only work on one table at a time. Cursors have their place -- it's just a much smaller place than they often inhabit.
- Les
December 1, 2009 at 6:15 pm
Actually, it's SQL2K.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply