How to write query to Delete the records in child table
and corresponding records in parent table
Answers were Sorted based on User's Feedback
Answer / arijitnit
delete from child_table where values=...
on delete cascade..
U have to set the foreign key as the parent super key
Is This Answer Correct ? | 11 Yes | 7 No |
Answer / dileep
Better you create a Trigger on child table like this one
CREATE TRIGGER trDelTrigger ON [dbo].[ChildTable]
FOR DELETE
AS
DELETE FROM ParentTable WHERE ParentTable.IdColumn=
(SELECT Deleted.IdColumn FROM Deleted)
Is This Answer Correct ? | 9 Yes | 5 No |
Answer / krishna evsg
With help of temporary tables , we can do it made easy
create table #temp_ParentIDs
(
ID int
)
GO
Insert into #temp_ParentIDs
(ID)
select Parent_id from child_tab
GO
delete from Child_tab
GO
delete from parent_tab where parent_Id In(select * from
#temp_ParentIDs)
GO
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / krishnakumar
create table employee(id int references authors(au_id) ON
DELETE CASCADE,firstname(30))
this is chilld table .in this query use on first child table
constraints values delete next parent table constrains value
delete ... this is ON DELETE CASCADE CONCEPT
Is This Answer Correct ? | 7 Yes | 5 No |
Answer / chaitanya
***********
Delete from P_table where P_Id=C_Id
***********
This is only aplicable if you have defined the foreign key
in the child table with the constraint on Delete Cascade.
Else you will get a Integration error.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / satyanarayana
I think using ON DELETE CASCADE we can delete the records
from both child and parent table
Is This Answer Correct ? | 4 Yes | 4 No |
Answer / tester
Hi vaishali have u executed the query it is not working
Is This Answer Correct ? | 6 Yes | 9 No |
Answer / surya
use this:
DELETE FROM childTable WHERE EXISTS(SELECT id FROM
parentTable WHERE parentTable .id = childTable .id where id = 1)
Is This Answer Correct ? | 4 Yes | 7 No |
Answer / sanjay
delete details from details inner join master on
details.keyid=master.keyid
Is This Answer Correct ? | 0 Yes | 4 No |
Answer / zackziss
delete from c from child c inner join parent
on c.childid = parentid
Is This Answer Correct ? | 2 Yes | 11 No |
How to install sql server 2005 express edition?
What sql server means?
How to call a function from a stored procedure in SQL Server ?
What are the basic functions for master, msdb, model, tempdb databases?
What is the diff between Static Queries and Dynamic queries give me some examples
Why would you call update statistics?
How to get a list of columns in a view using "sys.columns" in ms sql server?
How to rename databases in ms sql server?
What is the security principal at the database level that represents your session?
What is indexing in sql server with example?
What is the difference between login and a user?
13 Answers CTS, TCS,
What is checkpoint process in the sql server?