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.

• Yahfoufi: The best way is to create a dynamic sql query to do a similar query. it is faster than using loops and cursors or writing the whole commands. you can find an example at the second answer here. try to write a similar logic

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

enter image description here

• l.lijith: Thank you for your response, The above given scripts can be used to delete records from two table.If we have more than two tables say for 100 tables?
• HEDMON: add 98 semicolons to the script :) how said @SmokingDBA "You are going to have to explicitly list the tables from which you need to delete anyway" but I recommend you read the first link. If the tables have some strong relationship, deleting from one, will delete from others... but that will depend from the db architecture.
• Nic: Question is flagged with SQL Server which does not support this sort of delete statement. See learn.microsoft.com/en-us/sql/t-sql/statements/… for the supported delete functionality.
• HEDMON: which concretely statement is not supported?