Need help in update query

• v.bragadeesh

  SSC Rookie

  Points: 40

  More actions

  May 23, 2009 at 1:26 am

  #209901

  hi all,

  I have multiple tables in my database.in all tables i have one common field called Companyname.

  it is possible to update company name in all tables using single query.

  Thanks in advance.

  Bragadeesh.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  May 23, 2009 at 2:16 am

  #999287

  Hi,

  Try this

  select ('update '+Table_Name+char(13)+' SET COMPANY_code = '+''''+'New Value'+''''+' Go' ) col1 into #temp

  from INFORMATION_SCHEMA.COLUMNS

  where Column_name like 'COMPANY_code'--COMPANY_NO,COMPANY

  --select * from #temp

  declare @sql varchar(8000)

  set @sql = ''

  select @sql = @sql + col1+char(13) from #temp

  select @sql

  EXEC (@SQL)

  you have fuge number fo tables,then you put part by part run the exec,

  because the @sql declared upto 8000 of max char.

  ARUN SAS

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 23, 2009 at 2:16 am

  #999288

  Hi

  You can create a dynamic SQL Statement:

  DECLARE @sql NVARCHAR(4000)

  DECLARE @newValue NVARCHAR(100)

  SELECT

  @sql = '',

  @newValue = 'bah'

  SELECT @sql = @sql + N'UPDATE ' + TABLE_NAME + N' SET CompanyName = ' + QUOTENAME(@newValue, '''') + N' WHERE 1 = 2' + NCHAR(10)

  FROM INFORMATION_SCHEMA.COLUMNS

  WHERE COLUMN_NAME = 'CustomerId'

  PRINT (@sql)

  -- Un-comment this to execute the dynamic statement

  --EXECUTE (@sql)

  Greets

  Flo

  Edited: Had an error in the QUOTENAME

• v.bragadeesh

  SSC Rookie

  Points: 40

  More actions

  May 23, 2009 at 3:15 am

  #999293

  Thanks all, its working

  Bragadeesh

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  May 23, 2009 at 9:40 pm

  #999411

  v.bragadeesh (5/23/2009)

  ---

  Thanks all, its working

  Bragadeesh

  Heh... What is working? Please post your final code.

  --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)