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

  • Using Dynamic Query along with the following objects we can update tables, provided that ID field exists in all the tables. We can also delete tables.

    SELECT * FROM information_schema.tables

    or

    SELECT * FROM sysobjects WHERE xtype='U'

    Refer this link http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/


    Regards, RSingh



    Thursday, October 10, 2013 3:36 AM
  • 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
  • If the ID is PRIMARY KEY & FOREIGN KEY, then the best method is table definition with cascading:

    BOL: "Cascading Referential Integrity Constraints

    Cascading referential integrity constraints allow you to define the actions Microsoft® SQL Server™ 2000 takes when a user attempts to delete or update a key to which existing foreign keys point.

    The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:

    • [ ON DELETE { CASCADE | NO ACTION } ]
    • [ ON UPDATE { CASCADE | NO ACTION } ]"

    LINK: http://technet.microsoft.com/en-us/library/aa933119(v=SQL.80).aspx


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, October 10, 2013 10:19 AM
    Moderator
  • If all tables are related to each other using primary or foreign key. It would be good to use CASCADE delete or update to remove/update across all tables.

    Thursday, October 10, 2013 12:56 PM