Help? - 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

General SQL Server Forums

New to SQL Server Programming

Help?

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-12-31 : 03:33:04

how to delete a rows from child table and its corresponding parent table ?
i tried with the below query,but it is not working can anyone help??

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-12-31 : 03:34:22

sorry t post the query..
DELETE FROM
TEST2 t,dum1 d
where d.empid=t.empid1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-31 : 03:45:07

do it in 2 queries. child records first, then parent.

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 03:46:29

suppose you have two tables
child table
TEST2
ID Dept empid1
100 Maths 130
120 Physics 125
125 Chemistry 130
....

and its parent table
dum1
empid name address
125 Rick Add1
130 Mary Add2
145 Steve Add3

and you need to delete the records of emp id 130 from both tables.
Then use:-

DELETE t FROM
TEST2 t
INNER JOIN dum1 d
ON d.empid=t.empid1
WHERE empid=130

DELETE FROM dum1 WHERE empid=130

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 03:49:14

And to do this automatically you can specify the clause ON DELETE CASCADE while defining the foreign key of the child table in which case on deletion of a record from parent table all child table records linked to this record will automatically be deleted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources