TSQL help to delete

17 Mar 2006 3:35 PM

DXC

I have 6 tables which 5 of them has foreign key to 1 table.
t1, t2, t3, t4, t5, t6 where t2, t3, t4, t5, t6 have foreign keys
referencing a column in t1 (version_number).

How can I delete a record from all 6 in 1 TSQL statement starting from the
child tables finishing with parent table (t1) ?

Thanks.

17 Mar 2006 8:28 PM

Daniel Jameson

DXC,

Change the foreign keys to "DELETE CASCADE." Then when you delete the t1
record, the t2...t6 records will be automatically deleted.

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Show quoteHide quote

"DXC" <D**@discussions.microsoft.com> wrote in message
news:05D33420-0B36-4740-881E-EE513D77830C@microsoft.com...
>I have 6 tables which 5 of them has foreign key to 1 table.
> t1, t2, t3, t4, t5, t6 where t2, t3, t4, t5, t6 have foreign keys
> referencing a column in t1 (version_number).
>
> How can I delete a record from all 6 in 1 TSQL statement starting from the
> child tables finishing with parent table (t1) ?
>
> Thanks.
>

20 Mar 2006 7:36 AM

Steen Persson (DK)

DXC wrote:

> I have 6 tables which 5 of them has foreign key to 1 table.
> t1, t2, t3, t4, t5, t6 where t2, t3, t4, t5, t6 have foreign keys
> referencing a column in t1 (version_number).
>
> How can I delete a record from all 6 in 1 TSQL statement starting from the
> child tables finishing with parent table (t1) ?
>
> Thanks.
>

If you don't want to (0r can't) use DELETE CASCADE as suggested you'll
have to delete the records in the child tables "manually".
There are no automated way to do this, but it should be fairly simple if
you use the EXISTS keyword.

Something like this should do it

DELETE FROM t6 WHERE EXISTS(SELECT * FROM t1 WHERE t1.version_number =
t6.version_number)

DELETE FROM t5 WHERE EXISTS(SELECT * FROM t1 WHERE t1.version_number =
t5.version_number)

......

DELETE FROM t1

Regards
Steen

22 Mar 2006 12:49 AM

Daniel Jameson

DK,

A) I don't think DXC wanted to completely empty his/her tables, which your
code would do.

B) Since there are foreign keys, the data in t6 has to match "WHERE
EXISTS(SELECT * FROM t1 WHERE t1.version_number = t6.version_number)", and,
thus, the EXISTS clause is redundant and non-functional.

C) Thus, s/he would just run the simple statements (which it was the point
to avoid):
DELETE FROM t6 WHERE version_number = @version_number

DELETE FROM t5 WHERE version_number = @version_number
....

DELETE FROM t1 WHERE version_number = @version_number

D) There is a way to do it with an INSTEAD OF DELETE trigger if you can't
implement DELETE CASCADE foreign keys. Let me know if that is of interest.

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Show quoteHide quote

"Steen Persson (DK)" <s**@REMOVEdatea.dk> wrote in message
news:%236KmDE$SGHA.1868@TK2MSFTNGP09.phx.gbl...
> DXC wrote:
>> I have 6 tables which 5 of them has foreign key to 1 table.
>> t1, t2, t3, t4, t5, t6 where t2, t3, t4, t5, t6 have foreign keys
>> referencing a column in t1 (version_number). How can I delete a record
>> from all 6 in 1 TSQL statement starting from the child tables finishing
>> with parent table (t1) ?
>>
>> Thanks.
>>
> If you don't want to (0r can't) use DELETE CASCADE as suggested you'll
> have to delete the records in the child tables "manually".
> There are no automated way to do this, but it should be fairly simple if
> you use the EXISTS keyword.
>
> Something like this should do it
>
> DELETE FROM t6 WHERE EXISTS(SELECT * FROM t1 WHERE t1.version_number =
> t6.version_number)
>
> DELETE FROM t5 WHERE EXISTS(SELECT * FROM t1 WHERE t1.version_number =
> t5.version_number)
>
> .....
>
> DELETE FROM t1
>
> Regards
> Steen

22 Mar 2006 8:33 PM

Steen Persson (DK)

Daniel Jameson wrote:
Show quoteHide quote

> DK,
>
> A) I don't think DXC wanted to completely empty his/her tables, which your
> code would do.
>
> B) Since there are foreign keys, the data in t6 has to match "WHERE
> EXISTS(SELECT * FROM t1 WHERE t1.version_number = t6.version_number)", and,
> thus, the EXISTS clause is redundant and non-functional.
>
> C) Thus, s/he would just run the simple statements (which it was the point
> to avoid):
> DELETE FROM t6 WHERE version_number = @version_number
>
> DELETE FROM t5 WHERE version_number = @version_number
> ...
>
> DELETE FROM t1 WHERE version_number = @version_number
>
> D) There is a way to do it with an INSTEAD OF DELETE trigger if you can't
> implement DELETE CASCADE foreign keys. Let me know if that is of interest.
>

Hi

I also doubt that the want want to empty all the tables, but he didn't
really supply any info on this...;-).

I admit that the code examples wasn't the best ones, but if he has the
search conditions in the t1 table (e.g. a range of customer number ), he
can put in the WHERE EXISTS clause. With your examples he can only
delete based on version number, and that might not be what he want.

Regards
Steen