Help need to delete a record from Referenced table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help need to delete a record from Referenced table

Author

Topic

turbo
Starting Member

19 Posts

Posted - 2010-07-30 : 07:58:07

I need to delete a singe record from a table A ,(input like ‘TABLENAME, Sno’) since the table A has N number of child table referenced to it . For example
A --referenced-- > B and C table
C --referenced-- > D table.

The solution is that , i have to delete the record first from the D table then from the C table .
And delete record from B table and at last delete the record from A table.
I have made some advance ment in the code by find the child of a table.
SELECT

FK_Table = FK.TABLE_NAME,

FK_Column = CU.COLUMN_NAME,

PK_Table = PK.TABLE_NAME,

PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

INNER JOIN (

SELECT i1.TABLE_NAME, i2.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

) PT ON PT.TABLE_NAME = PK.TABLE_NAME

where pk.table_name= 'TABLENAME'

Pls help!!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-30 : 08:05:44

quote:
The solution is that , i have to delete the record first from the D table then from the C table .
And delete record from B table and at last delete the record from A table.

Yes - and what is your question/problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 08:20:03

[code]
DELETE D
FROM TableA AS A
LEFT OUTER JOIN TableB AS B
ON B.ID = A.ID
LEFT OUTER JOIN TableC AS C
ON C.ID = B.ID
LEFT OUTER JOIN TableD AS D
ON D.ID = C.ID

DELETE C
FROM TableA AS A
LEFT OUTER JOIN TableB AS B
ON B.ID = A.ID
LEFT OUTER JOIN TableC AS C
ON C.ID = B.ID
LEFT OUTER JOIN TableD AS D
ON D.ID = C.ID

DELETE B
...
[/code]
You can leave out the JOIN'd tables that are not relevant at each level, or leave them in as shown (which is why I have used OUTER JOINs)

turbo
Starting Member

19 Posts

Posted - 2010-07-30 : 09:26:30

Finished the code .. thanks to all

create proc delete_records

(
@inv_tablename varchar(max), /* name of the table where rows are to be deleted */
@inv_criteria nvarchar(max), /* criteria used to delete the rows required */
@oi_rowsaffected bigint output /* number of records affected by the delete */
)
as

begin
begin try
begin tran
set nocount on
declare @ctab varchar(max), /* name of the child table */
@ccol varchar(max), /* name of the linking field on the child table */
@creftab varchar(max), /* name of the parent table */
@crefcol varchar(max), /* name of the linking field in the parent table */
@cfkname varchar(max), /* name of the foreign key */
@csql nvarchar(max), /* query string passed to the sp_executesql procedure */
@cchildcriteria nvarchar(max), /* criteria to be used to delete records from the child table */
@ichildrows bigint /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */
declare cfkey cursor local for

select so1.name as tab, sc1.name as col, so2.name as reftab, sc2.name as refcol, fo.name as fkname
from dbo.sysforeignkeys fk
inner join dbo.syscolumns sc1 on fk.fkeyid = sc1.id and fk.fkey = sc1.colid
inner join dbo.syscolumns sc2 on fk.rkeyid = sc2.id and fk.rkey = sc2.colid
inner join dbo.sysobjects so1 on fk.fkeyid = so1.id
inner join dbo.sysobjects so2 on fk.rkeyid = so2.id
inner join dbo.sysobjects fo on fk.constid = fo.id
where so2.name = @inv_tablename

open cfkey
fetch next from cfkey into @ctab, @ccol, @creftab, @crefcol, @cfkname
while @@fetch_status = 0
begin
/* build the criteria to delete rows from the child table. as it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
set @cchildcriteria = @ccol + ' in (select [' + @crefcol + '] from [' +
@creftab +'] where ' +@inv_criteria+ ')'
print 'deleting records from table ' + @ctab
/* call this procedure to delete the child rows */

exec delete_records @ctab, @cchildcriteria, @ichildrows output
fetch next from cfkey into @ctab, @ccol, @creftab, @crefcol, @cfkname
end
close cfkey
deallocate cfkey
/* finally delete the rows from this table and display the rows affected */
set @csql = 'delete from [' + @inv_tablename + '] where ' +@inv_criteria
print @csql
exec sp_executesql @csql
print 'deleted ' + convert(varchar, @@rowcount) + ' records from table ' + @inv_tablename
commit tran
end try

begin catch
print error_message()
rollback tran
end catch

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources