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 do you test proper tcp/ip configuration windows machine? : Sql server database administration
What are the new data types are introduced in sql 2000?
How many types of stored procedures are there in sql server?
How to find the date and time of last updated table?
Explain the stored procedure?
Which are the new data types introduced in sql server 2008?
Explain nested join?
i have a table #temp1(id, Name groupname ) and record like this 1 R1 S 2 R3 S 3 R2 S 4 R4 D 5 R5 D 6 R6 K 7 R7 K 8 R8 L 9 R9 L 10 R10 L 11 R11 K and i want to display record based on user defind sorting order e.g. 1 R4 D 2 R5 D 3 R6 K 4 R7 K 5 R11 K 6 R1 S 7 R3 S 8 R2 S 9 R8 L 10 R9 L 11 R10 L
How to find the version of sql server? : sql server database administration
What is star, snowflake and star flake schema? : sql server analysis services, ssas
Why can there be only one clustered index and not more than one?
What are commonly used mssql functions in php?