Post reply

Need help in update query

  • 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.

  • 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

  • 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

  • May 23, 2009 at 3:15 am

    #999293

    Thanks all, its working

    Bragadeesh

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

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

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