Delete data in table - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Delete data in table

Author

Topic

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-11-07 : 05:19:14

Hi to all,

I have to delete some rows from a table based on timestamp, but this table has foreign key relationship with other table.

So before I can delete rows from this table, I need to delete related rows from other table.

But this other table also has foreign key relationship with third table, and third table with 4th table.

So basically I will need to delete the rows from 4th Table, before I can delete third table rows, and so on.

What is the best way to achieve this.

Thanks

Manju

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-07 : 06:23:05

The best way to achieve this is to delete int he correct order or have a cascade delete.

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-11-07 : 06:27:09

Hi Rickd,

Thanks for reply,

could you please tell how can i do that ?

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 06:42:44

you can use below query to find the dependeny objects from parent till last child


;WITH table_dependencies
AS
(
SELECT object_name(object_Id) AS table_name,object_Id,1 AS level
from sys.objects
WHERE object_name(object_Id)= <yourtablenamehere> 
union all
SELECT object_name(f.referenced_object_Id),f.referenced_object_Id,d.Level + 1
FROM sys.foreign_keys f
JOIN table_dependencies d
ON f.parent_object_id = d.object_Id
)

SELECT  table_name
FROM table_dependencies
order by Level DESC

once you get this list add delete from table where statements to delete data from tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources