EXISTS or other construct to achieve FK table record deletion

I need one other favor. I am trying to delete the detail records from the
detail table before deleting the header record from the pk table. I have pu
t
together the following SQL. The SELECT statement gets the 2 records I need
from the detail table, but I'm not sure how to pass the ID's to the DELETE
part of the statement to get id of the details records. I have the overall
statement as a SELECT for now, until I work it all out. I will then change
the SELECT to a DELETE. I have included both versions for your review. I
would certainly appreciate your assistance. I can give you my phone number
if you'd like to speak in person more about my effort.
Gratefully,
Mark
CODE SAMPLE:
--
SELECT *
FROM jrnDTLGrantManagement AS Tdtl
WHERE EXISTS
-->this part gets the 2 records I'm looking for but the above returns 8000 +
record
-->ignoring the criteri below. Am I using the EXISTS incorrectly
-->TSum.IDJ is the PK below
-->Tdtl.IDJ is the FK
(SELECT * FROM jrnGrantManagement AS Tsum
INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
WHERE TInit.ParentH1 = 'CanadianBorder'
AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')
when the above works, I plan to convert it to a DELETE statement as follows:
DELETE jrnDTLGrantManagement
FROM jrnDTLGrantManagement AS Tdtl
WHERE EXISTS
(SELECT * FROM jrnGrantManagement AS Tsum
INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
WHERE TInit.ParentH1 = 'CanadianBorder'
AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')You should be able to accomplish this by creating a relationship with
"Cascade Delete Related Fields" selected.
This should let the database handle the deletes in the detail table
automatically when a record from the PK table is deleted. This requires no
coding, just point and click.
That said, if you really want to do it this way, here are a couple of
suggestions...
First, replace the * in your exists select with a litteral 1. I'm not
certain how SQL Server handles this, btu when I worked with Oracle selecting
1 rather than fields was less overhead, as no data had to actually be placed
in memory save the one. If anyone knows if this really makes a difference
or not, I would love to knwo for sure.
It looks like the problem with your exists is that you are not joining it to
your table in your select statement, so it is finding one row exists
regardless of which row you are looking at in the main select. Try adding a
criteria to your exists where it joins to the outer select (delete).
where TDtl2.Idj = TDtl.Idj
You could also make your exists an in clause, and select TDtl2.Idj in place
of *. Then just chang "where exists" to "where TDtl.Idj in" I'm not sure
how this will affect performance as I dont know your table structures.
Hope this helps.
"Mark in Miami" <MarkinMiami@.discussions.microsoft.com> wrote in message
news:BE80A9BA-401A-4E73-B4AC-4093F72A6D8B@.microsoft.com...
> I need one other favor. I am trying to delete the detail records from the
> detail table before deleting the header record from the pk table. I have
put
> together the following SQL. The SELECT statement gets the 2 records I
need
> from the detail table, but I'm not sure how to pass the ID's to the DELETE
> part of the statement to get id of the details records. I have the
overall
> statement as a SELECT for now, until I work it all out. I will then
change
> the SELECT to a DELETE. I have included both versions for your review. I
> would certainly appreciate your assistance. I can give you my phone
number
> if you'd like to speak in person more about my effort.
> Gratefully,
> Mark
> CODE SAMPLE:
> --
> SELECT *
> FROM jrnDTLGrantManagement AS Tdtl
> WHERE EXISTS
> -->this part gets the 2 records I'm looking for but the above returns 8000
+
> record
> -->ignoring the criteri below. Am I using the EXISTS incorrectly
> -->TSum.IDJ is the PK below
> -->Tdtl.IDJ is the FK
> (SELECT * FROM jrnGrantManagement AS Tsum
> INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
> INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
> WHERE TInit.ParentH1 = 'CanadianBorder'
> AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')
> when the above works, I plan to convert it to a DELETE statement as
follows:
> DELETE jrnDTLGrantManagement
> FROM jrnDTLGrantManagement AS Tdtl
> WHERE EXISTS
> (SELECT * FROM jrnGrantManagement AS Tsum
> INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
> INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
> WHERE TInit.ParentH1 = 'CanadianBorder'
> AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')
>|||DELETE jrnDTLGrantManagement
FROM jrnDTLGrantManagement AS Tdtl
WHERE EXISTS
(SELECT * FROM jrnGrantManagement AS Tsum
INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
WHERE TInit.ParentH1 = 'CanadianBorder'
AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06'
)
This makes it a correlated subquery, and should work. First of course,
check my work by executing:
SELECT Tdtl.*
FROM jrnDTLGrantManagement AS Tdtl
WHERE EXISTS
(SELECT * FROM jrnGrantManagement AS Tsum
INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
WHERE TInit.ParentH1 = 'CanadianBorder'
AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06'
AND TSum.Idj = TDtl.Idj
)
And make sure the rows that are returned are the ones you actually want
deleted. (Since you didn't give me a table or data to test out, I may have
made a mistake!)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mark in Miami" <MarkinMiami@.discussions.microsoft.com> wrote in message
news:BE80A9BA-401A-4E73-B4AC-4093F72A6D8B@.microsoft.com...
>I need one other favor. I am trying to delete the detail records from the
> detail table before deleting the header record from the pk table. I have
> put
> together the following SQL. The SELECT statement gets the 2 records I
> need
> from the detail table, but I'm not sure how to pass the ID's to the DELETE
> part of the statement to get id of the details records. I have the
> overall
> statement as a SELECT for now, until I work it all out. I will then
> change
> the SELECT to a DELETE. I have included both versions for your review. I
> would certainly appreciate your assistance. I can give you my phone
> number
> if you'd like to speak in person more about my effort.
> Gratefully,
> Mark
> CODE SAMPLE:
> --
> SELECT *
> FROM jrnDTLGrantManagement AS Tdtl
> WHERE EXISTS
> -->this part gets the 2 records I'm looking for but the above returns 8000
> +
> record
> -->ignoring the criteri below. Am I using the EXISTS incorrectly
> -->TSum.IDJ is the PK below
> -->Tdtl.IDJ is the FK
> (SELECT * FROM jrnGrantManagement AS Tsum
> INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
> INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
> WHERE TInit.ParentH1 = 'CanadianBorder'
> AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')
> when the above works, I plan to convert it to a DELETE statement as
> follows:
> DELETE jrnDTLGrantManagement
> FROM jrnDTLGrantManagement AS Tdtl
> WHERE EXISTS
> (SELECT * FROM jrnGrantManagement AS Tsum
> INNER JOIN jrnDTLGrantManagement AS TDtl2 ON TSum.Idj = TDtl2.Idj
> INNER JOIN mbrInitiative AS TInit ON Tdtl2.Initiative = TInit.ID
> WHERE TInit.ParentH1 = 'CanadianBorder'
> AND CONVERT(CHAR(8),TSum.DateModified,10) = '01-11-06')
>|||Do what people have told you, then add a REFERENCES.. ON DELETE CASCADE
clause to the referencing table. Also, please learn why rows are not
anythignlike records, and you will not findyourself in this situation
again
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||I should have mentioned this, but only if it truly makes sense. He might
just be doing some data cleanup, or a one-time thing. I usually don't
suggest using cascade (I assume that the FK was in place) unless you truly
want it to happen with no warning whatsoever (which is not always desired.)
Then you can write this sort of code to execute, as it is basically the same
thing that the FK will do anyhow.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:uQy6ZWtFGHA.3100@.tk2msftngp13.phx.gbl...
> Do what people have told you, then add a REFERENCES.. ON DELETE CASCADE
> clause to the referencing table. Also, please learn why rows are not
> anythignlike records, and you will not findyourself in this situation
> again
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***