Delete records from three tables in sql server

In my asp.net Web application, I have 3 tables Table 1,Table 2 and Table 3.

Table 1 Table 2 Table 3

Id AudioId AudioId

AudioName AudioName AudioName

These are my Tables and contains fields here Table 1 Id is same as AudioId in Table 2 and Table 3. Here based on Table 1 Id when i select that record and click delete it automatically delete that Id based on records in table 2 and table 3.

How can i do this can anyone please help me out.

Thank you

Assume that you have a foreign key relationship , if not then 1st create the relation in those tables

-IF YOU WANT TO DELETE ALL RECORDS FROM TABLE THEN PASS 1=1 OR IF YOU WANT TO DELETE RECORDS BASED ON CODITION THEN PASS column_name = value like user_id = 2

--SO YOUR QUERY LIKE EXEC pr_delete_child_records 'parent_table', '1 = 1' FOR ALL

--SO YOUR QUERY LIKE EXEC pr_delete_child_records 'parent_table', 'column_name = value' FOR ALL

CREATE PROCEDURE [dbo].[pr_delete_child_records]

@tableName varchar(50),

@criteria nvarchar(MAX)

BEGIN

DECLARE @foreign_key_name VARCHAR(200),

@foreign_table VARCHAR(200),

@foreign_column VARCHAR(200),

@parent_table VARCHAR(200),

@parent_column VARCHAR(200),

@child_criteria VARCHAR(MAX),

@cSQL VARCHAR(MAX)

DECLARE l_c CURSOR LOCAL FOR

SELECT p_table.name AS parent_table,

p_column.name AS parent_column,

Fk_table.name AS foreign_table,

fk_column.name AS foreign_column,

Fk_name.name AS foreign_key_name

FROM dbo.sysforeignkeys FK

INNER JOIN dbo.syscolumns p_column ON FK.fkeyid = p_column.id AND FK.fkey = p_column.colid

INNER JOIN dbo.syscolumns fk_column ON FK.rkeyid = fk_column.id AND FK.rkey = fk_column.colid

INNER JOIN dbo.sysobjects p_table ON FK.fkeyid = p_table.id

INNER JOIN dbo.sysobjects Fk_table ON FK.rkeyid = Fk_table.id

INNER JOIN dbo.sysobjects Fk_name ON FK.constid = Fk_name.id

WHERE Fk_table.Name = @tableName

OPEN l_c

FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @child_criteria = @parent_column + ' in (SELECT [' + @foreign_column + '] FROM [' +

@foreign_table +'] WHERE ' + @criteria + ')'

EXEC pr_delete_child_records @parent_table, @child_criteria

PRINT @parent_table + ' ' + @child_criteria

FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name

END

CLOSE l_c

DEALLOCATE l_c

SET @cSQL = 'DELETE FROM [' + @tableName + '] WHERE ' + @criteria

PRINT @cSQL

EXEC (@cSQL)

END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
If you don't already have a foreign key constraints just add it with ON DELETE CASCADE clause and SQL Server will automatically delete rows from all referenced tables when a row is deleted from main table. No coding is required whatsoever, except above of course!

Please refer to the following for the detailed discussion on the topic.

http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server
sudipta sanyal9y ago
Assume that you have a foreign key relationship , if not then 1st create the relation in those tables

-IF YOU WANT TO DELETE ALL RECORDS FROM TABLE THEN PASS 1=1 OR IF YOU WANT TO DELETE RECORDS BASED ON CODITION THEN PASS column_name = value like user_id = 2
--SO YOUR QUERY LIKE EXEC pr_delete_child_records 'parent_table', '1 = 1' FOR ALL
--SO YOUR QUERY LIKE EXEC pr_delete_child_records 'parent_table', 'column_name = value' FOR ALL
CREATE PROCEDURE [dbo].[pr_delete_child_records]
@tableName varchar(50),
@criteria nvarchar(MAX)
AS
BEGIN
DECLARE @foreign_key_name VARCHAR(200),
@foreign_table VARCHAR(200),
@foreign_column VARCHAR(200),
@parent_table VARCHAR(200),
@parent_column VARCHAR(200),
@child_criteria VARCHAR(MAX),
@cSQL VARCHAR(MAX)
DECLARE l_c CURSOR LOCAL FOR
SELECT p_table.name AS parent_table,
p_column.name AS parent_column,
Fk_table.name AS foreign_table,
fk_column.name AS foreign_column,
Fk_name.name AS foreign_key_name
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns p_column ON FK.fkeyid = p_column.id AND FK.fkey = p_column.colid
INNER JOIN dbo.syscolumns fk_column ON FK.rkeyid = fk_column.id AND FK.rkey = fk_column.colid
INNER JOIN dbo.sysobjects p_table ON FK.fkeyid = p_table.id
INNER JOIN dbo.sysobjects Fk_table ON FK.rkeyid = Fk_table.id
INNER JOIN dbo.sysobjects Fk_name ON FK.constid = Fk_name.id
WHERE Fk_table.Name = @tableName
OPEN l_c
FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @child_criteria = @parent_column + ' in (SELECT [' + @foreign_column + '] FROM [' +
@foreign_table +'] WHERE ' + @criteria + ')'

EXEC pr_delete_child_records @parent_table, @child_criteria
PRINT @parent_table + ' ' + @child_criteria
FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name
END
CLOSE l_c
DEALLOCATE l_c
SET @cSQL = 'DELETE FROM [' + @tableName + '] WHERE ' + @criteria
PRINT @cSQL
EXEC (@cSQL)
END