cascade delete - 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 2000 Forums

SQL Server Development (2000)

cascade delete

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-22 : 10:40:27

I don't know how the cascade delete statement look like, I just assume just like the regular delete statement? The query below is a cascade delete ? if not then please show me how cascade delete look like?

Thanks a lot

=============
Delete tbl1
from tbl1 join tbl2
On tbl1.id=tbl2.id
Where tbl2.userid = @userid

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-22 : 10:50:48

look at foreign keys in the bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-22 : 11:58:49

I look bol on your sugesstion and I am not quite understand at all. Here what I try bt cna't work. I think i did wrong but don't know how, please help

Delete table2
From table2 JOIN table1
On table2.userid = table1.userid
where DELETE Cascade
and table1.id = 20

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-11-22 : 13:24:51

cascading delete is an option when defining a foreign key. i.e

create table pk(pk int primary key)

create table fk(fk int references pk on delete cascade)

insert into pk values(1)
insert into fk values(1)
delete from pk where pk = 1

the last delete will also delete records from the fk table where fk = 1

It has nothing to do with the delete statement per se.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-22 : 14:42:16

Thanks alot

Ok, I set the option "Cacasede Deleted Ralated Records" from the tabble design. When I set that option, all i do just a simple delete query from parents table and and it was delete all the child tables too. But here is my quesiton when I try to delete more than one records at the time

delete from tbl
where id in(@id)

Id: 1,2,3, but I can't type one by one id because i have 55,000 id need to be delte. I created the temp table to have that 55,000 id but don't know how to make it refrence to my delete query to delete all
Thanks alot

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-23 : 03:22:10

Very simple just write this query

delete from tbl
where id in(Select tempid from temp)

This will solve your problem.

anwer

quote:

Thanks alot

Ok, I set the option "Cacasede Deleted Ralated Records" from the tabble design. When I set that option, all i do just a simple delete query from parents table and and it was delete all the child tables too. But here is my quesiton when I try to delete more than one records at the time

delete from tbl
where id in(@id)

Id: 1,2,3, but I can't type one by one id because i have 55,000 id need to be delte. I created the temp table to have that 55,000 id but don't know how to make it refrence to my delete query to delete all
Thanks alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources