none
How to delete or Update data in Multiple table in DB

    Question

  • Dear All,

    In SQL Management Studio 2008 has one of the Database with 138 tables. May i know how to update or delete the data into all the tables ?

    example -  if update the ID in one short into all the tables 

    kindly advise, thank you. 

    Thursday, October 10, 2013 2:37 AM

Answers

  • Hi

    PFB code

    --method 1 
    EXEC sp_MSforeachtable 'UPDATE a set ID=2 from ? a where ID=3'
    
    --method 2
    Declare @output varchar(max)
    select @output =''
    SELECT @output += CHAR(13) + CHAR(10) 
      + 'Update a set a.'+c.name+' =2 from ' + t.name + ' a where '+c.name+'=3'+' ;'
    
      FROM sys.tables AS t
      INNER JOIN sys.columns AS c
      ON t.[object_id] = c.[object_id]
      WHERE c.name = 'ID';
      
    Print @output;
    -- EXEC sp_executesql @output;

    Thanks

    Saravana Kumar C

    Thursday, October 10, 2013 3:38 AM

All replies