Delete records from table (3 level of Inner Join)

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

Delete records from table (3 level of Inner Join)

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-10-18 : 05:20:41

Dear Gurus,

I have a Purchase Order System.

Level1:
PR_Headers Table
[PR_HeaderID] - PK
[PR_HeaderDesc]

Level2:
PR_Details Table
[PR_DetailID] - PK
[PR_DetailDesc]
[PR_DetailQuantity]
[PR_HeaderID] - FK

Level3:
PR_Details_received Table
[PR_Detail_ReceivedID] - PK
[PR_DetailQuantity_Received]
[PR_DetailID] - FK

1. Select Level 3 Table based on Level 1 HeaderID is easy, by using INNER JOIN:

SELECT 
L3.PR_details_receivedID
FROM PR_headers AS L1
INNER JOIN PR_details AS L2 ON L1.PR_headerID = L2.PR_headerID 
INNER JOIN PR_details_received AS L3 ON L2.PR_detailID = L3.PR_detailID
WHERE
(L1.PR_headerID = 5000)

2. But I can't figure out how about delete level 3 tables if I only provide the PR_headerID by not using a nested Query?

I want to
DELETE FROM PR_details_received WHERE PR_headers.PR_headerID = 5000
But I don't want to
DELETE FROM PR_details_received WHERE EXIST (SELECT....)

Please advise

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 05:39:57

if you've created fk with on delete cascade options it will take care of deletions by itself. but its not too great from maintenance aspect to use it though.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-10-18 : 05:42:23

argh! stupid me. I just figured it out I should DELETE INNER JOIN from Level 3 upwards to Level 1 instead of from Level 1 downwards to Level 3.

DELETE
FROM dbo.PR_details_received AS L3 INNER JOIN
dbo.PR_details AS L2 ON L3.PR_detailID = L2.PR_detailID INNER JOIN
dbo.PR_headers AS L1 ON L2.PR_headerID = L1.PR_headerID

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-10-18 : 06:11:45

Problem:
Select works fine, but if I changed the same Syntax to DELETE, it doesn't work. Please advise.

SELECT *
FROM PR_details_received INNER JOIN
PR_details ON PR_details_received.PR_detailID = PR_details.PR_detailID INNER JOIN
PR_headers ON PR_details.PR_headerID = PR_headers.PR_headerID
WHERE (PR_headers.PR_headerID = 5000)

DELETE
FROM PR_details_received
INNER JOIN PR_details ON PR_details_received.PR_detailID = PR_details.PR_detailID
INNER JOIN PR_headers ON PR_details.PR_headerID = PR_headers.PR_headerID
WHERE (PR_headers.PR_headerID = 5000)
Incorrect syntax near the keyword 'INNER'.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-10-18 : 06:22:03

finally got it...

DELETE PRDR
FROM PR_details_received as PRDR
INNER JOIN PR_details as PRD ON PRDR.PR_detailID = PRD.PR_detailID
INNER JOIN PR_headers as PRH ON PRD.PR_headerID = PRH.PR_headerID
WHERE (PRH.PR_headerID = @PR_headerID)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-18 : 07:04:09

You are not specifying which table you want to delete from in the last query. If you specify the table name to be deleted from right after the delete statement you should be fine:

DELETE PR_details_received
FROM ...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-18 : 07:05:39

Crap..I answered without checking for any new replies. I was away for some minutes. Good thing you figured it out :)

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-10-18 : 23:25:48

Lumbago,
LOL... no problem, often after I wrote and asked for help and advice, before anyone answered, often I figured out the answer in between the line when I read my own questions. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources