Updating Multiple Tables

Question

Post reply

Updating Multiple Tables

Jéan Moore

SSC Rookie

Points: 43
More actions
February 20, 2006 at 10:07 am

#112721

I'm trying to update a column in multiple tables all with a common column name. My statement looks as follows:
Declare @Table varchar(30)
declare csrTable Cursor for
SELECT     sysobjects.name
FROM         syscolumns INNER JOIN
                      sysobjects ON syscolumns.id = sysobjects.id
WHERE     (syscolumns.name = 'branch') AND (sysobjects.type = 'U')
open csrTable
while (1=1)
  begin
   fetch next from csrTable into @Table
    if (@@fetch_Status <> 0)
     Break
    Else
     UPDATE    @Table
     SET              branch = 3
     WHERE     (branch = 940)

     End
close csrTable
deallocate csrTable
When I go to parse the query, I get the following error:
Server: Msg 170, Level 15, State 1, Procedure sp_Mod_Branch, Line 18
Line 18: Incorrect syntax near '@Table'.
Can I use a variable for the table name? If so, what is its delcaration type?
In advance, I appreciate all of your assistance.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>Can I use a variable for the table name? If so, what is its delcaration type?

No, you need to build SQL in a string and execute it dynamically. Search the forums on keywords "dynamic SQL".

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

The most obvious solution is building the dynamic SQL piece by piece:

declare @cmd varchar(8000)

set @cmd=''

select @cmd=@cmd + 'UPDATE ' + quotename(table_schema) + '.' + quotename(table_name)

+ ' SET ' + quotename(column_name) + ' = 3 WHERE ' + quotename(column_name) + ' = 940); '

from information_schema.columns

where column_name = 'MarketID'

exec (@cmd)

I prefer using replaceable tags to make it more readable (IMHO):

declare @cmd varchar(8000)

set @cmd=''

select @cmd=@cmd + replace(replace(replace(replace(replace(

'UPDATE <schema>.<table> SET <col> = <newvalue> WHERE <col> = <oldvalue> '

,'<schema>',quotename(table_schema))

,'<table>',quotename(table_name))

,'<col>',quotename(column_name))

,'<oldvalue>',940)

,'<newvalue>',3)

from information_schema.columns

where column_name = 'MarketID'

exec (@cmd)

Krishnan Kaniappan Ten Centuries Points: 1051 More actions · Answer 3

just build your update statement string dynamically and execute the string using exec or sp_executesql...

-Krishnan

Jéan Moore SSC Rookie Points: 43 More actions · Answer 4

Thanks All! I got the gist of it. I appreciate all of your input and expertise.

Thanks again!!!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

Scott...

What editor are you using? Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

I copied the code from SQL 2005 Management Studio. You have to fix all the double-spaced lines, restore the indenting, change the font back to a non-proportional font, etc. But I like keeping the color-coding.