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 :
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)
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.
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
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
