Script to Delete records in multiple table

Question

I would like to delete a specific record from two or more tables which are linked to one another.

For Example: I have two tables, Students and winners I would like to delete the names Roy and Peter from both the tables in one shot.

table: Students

> ID     name      class
> 1      Roy         2
> 2      James       3
> 3      Carl        4
> 4      Peter       4
> 5      Alice       5

table: Winners

St_ID          achievement
1              1
2              1
3              3
4              5
5              5

I have more than 100 tables with 50 specific records to be deleted from all the tables.

Answer 1

You have to achieve this using Dynamic SQL Query

1- First you have to list all Tables With there Corresponding Databases in a Temp Table 2- Building Query for databases that contains Students and Winners dataTable

Your script should look like

--Get all Databases With corresponding Database

declare @SQL nvarchar(max)
DECLARE @strQuery AS NVARCHAR(MAX)

SET @strQuery = ''

CREATE TABLE #TblTemp (DatabaseName Varchar(255), Tablename Varchar(255))



set @SQL = (select 'union all 
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
INSERT INTO #TblTemp
exec (@SQL)

-- Building Queries

SELECT @strQuery = @strQuery + 'Delete T1 from [' + name  + '].dbo.Students As T2
                        Inner join  [' + name  + '].dbo.Winners as T1 
                        On T1.[st_ID] = T2.[ID] 
                        Where    T1.[name] = IN(''Roy'',''Peter'')  ;

DELETE FROM [' + name  + '].dbo.Students WHERE [name] = IN(''Roy'',''Peter'') ;

'

 from sys.databases
 WHERE EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Students') AND
 EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Winners')

--VIEW QUERIES (you can copy result and execute it manually)
SELECT @strQuery



--EXECUTE QUERIES
EXEC(@strQuery)

--DROP Temp Table 
DROP TABLE #TblTemp

This Will Result a query like the following (If these databases contains Students and Winners Table)

Delete T1 from [master].dbo.Students As T2   Inner join  [master].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [master].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [tempdb].dbo.Students As T2   Inner join  [tempdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [tempdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [model].dbo.Students As T2   Inner join  [model].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [model].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [msdb].dbo.Students As T2   Inner join  [msdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [msdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [AdventureWorks2008R2].dbo.Students As T2   Inner join  [AdventureWorks2008R2].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     DELETE FROM [AdventureWorks2008R2].dbo.Students WHERE [name] = IN('Roy','Peter') ;     Delete T1 from [DbMail].dbo.Students As T2   Inner join  [DbMail].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     

I don't Now if that is what you was asking for in your question below (Your question was not specific like this one, so my answer was providing the dynamic sql logic in general)

• Truncate with where clause

Answer 2

You are going to have to explicitly list the tables from which you need to delete anyway.

One of the scenarios would be to create a table with the list of tables you want to delete from and loop through each one of them applying appropriate delete statement based on id's of the students with specific names.

To loop through all tables in the database you may use something like this:

DECLARE c_tables CURSOR
FOR SELECT table_name
    FROM INFORMATION_SCHEMA.TABLES;

DECLARE
  @table_name VARCHAR(100);

OPEN c_tables;

FETCH NEXT FROM c_tables INTO
  @table_name;

WHILE @@FETCH_STATUS=0
BEGIN
--<your command here>
FETCH NEXT FROM c_tables INTO
  @table_name;
END;

CLOSE c_tables;

DEALLOCATE c_tables;

And do not forget to test delete in Test environment.

Answer 3

I think correct approach is to first Delete Record from Student table without dynamic sql and store the deleted ID in temp table.

like this,

DECLARE @DeletedSTID table(id int not null)

delete from Students
output deleted.id into @DeletedSTID
where name in('Peter','Roy')

select * from @DeletedSTID

In above example I do not have to use "where name in()" every time. I use it once and store it in temp table.

For Example I am using Adventure DB,

USE AdventureWorks2012
GO

CREATE TABLE #DeletedSTID (id INT NOT NULL)

CREATE TABLE #temp (tablename VARCHAR(100))

DECLARE @Sql VARCHAR(max) = ''

BEGIN TRY
    BEGIN TRANSACTION

    DELETE
    FROM HumanResources.EmployeeDepartmentHistory
    OUTPUT deleted.BusinessEntityID
    INTO #DeletedSTID
    WHERE BusinessEntityID IN (
            1
            ,2
            )

    INSERT INTO #temp
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'BusinessEntityID'
        AND TABLE_NAME NOT LIKE 'v%'

    SELECT *
    FROM #DeletedSTID

    SELECT *
    FROM #temp

    SELECT @Sql = @Sql + ' delete from ' + tablename + ' a where exists 
(select id from #DeletedSTID b where b.id=a.BusinessEntityID)  '
    FROM #temp

    PRINT @Sql

    EXEC (@Sql)

    ROLLBACK
        --COMMIT
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK

    SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

DROP TABLE #temp

DROP TABLE #DeletedSTID

Answer 4

From SQL DELETE

Most database management systems allow you to create a foreign key constraint so that if you delete a row in a table, the corresponding rows the related table are also removed automatically. This ensures the integrity of the data.

AND

Answered in SO. (Must be adapted to your situation)

Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

OR

Just use INNER JOIN as below

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

EDIT due to @NIC's comment (and DV?):

First option: table_constraint (Transact-SQL)

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

...

CASCADE Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

...

and an example in SO

Second option:

prepare tables

DROP TABLE IF EXISTS dbo.Student, dbo.Winner

CREATE TABLE dbo.Student 
(
    ID          INT         NOT NULL,
    name        VARCHAR(30) NOT NULL,
    class       VARCHAR(30) NOT NULL
);
CREATE TABLE dbo.Winner
(
    stID        INT         NOT NULL,
    achievement INT         NOT NULL
);
GO

INSERT INTO dbo.Student(ID, name, class)
VALUES
    (1, 'Roy', '2'),
    (2, 'James', '2'),
    (3, 'Carl', '2'),
    (4, 'Peter', '2'),
    (5, 'Alice', '2')

INSERT INTO dbo.Winner(stID, achievement)
VALUES
    (1, 1),
    (2, 1),
    (3, 3),
    (4, 5),
    (5, 5)

Deleting rows

DELETE FROM Student WHERE ID = 1;
DELETE FROM Winner WHERE stID = 1

Tested in SQL Server 13.0.1722.0 note: DROP TABLE IF EXIST may not work in older versions