• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

delete from two tables based on FK

I have two tables, tblOne (firstid (PK), secondid (FK)
tblTwo (secondid (PK), name, address)

I'm looking to create a DELETE statement that will delete from tblOne where firstid = @firstid, but then subsequently DELETE the related record from tblTwo based on the tblOne.secondid value of the deleted record. Can this be done in one statement?
0
alright
Asked:
alright
  • 5
  • 4
  • 2
1 Solution
 
AliSyedCommented:
You can set delete cascase and and it will be done automatically.
0
 
AliSyedCommented:
DELETE CASCADE
look at this
0
 
cyberkiwiCommented:
Not in one statement but can be done in the same batch in a transaction. Cascaded deletes in the other way, is not what you are after
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
cyberkiwiCommented:
Is it always one to one? Otherwise the delete from tbl2 may fail due to other records using that id.
0
 
alrightAuthor Commented:
Yes, always one to one
0
 
alrightAuthor Commented:
You say that CASCADE delete goes the other way, ie del tblTwo record first and then the associated tblOne record? If so, I think that would be okay
0
 
cyberkiwiCommented:
Then you can set up the foreign key to be the "master" of the relationship and cascade deletes, updates. Even then one statement may not be enough, if you have records in tblOne that have no secondid link.

alter table tblOne add constraint FK_onetwo foreign key
REFERENCES tblTwo(secondid) ON DELETE CASCADE ON UPDATE CASCADE;

Your statement:

Delete tblTwo where secondid in (select secondid from tblOne where <some condition>)

This deletes from tblTwo and will grab all the records from tblOne that link by FK, however, if there are records in tblOne that are not linked to tblTwo, you will need to fire a secondary delete

delete tblOne where <some condition>

So it may or may not be better than just managing the deletes in a batch transaction.
0
 
alrightAuthor Commented:
BEGIN TRAN
SET @secondid = tblOne.secondid WHERE firstid = @firstid
DELETE FROM [tblOne] WHERE [firstid] = @firstid
DELETE FROM [tblTwo] WHERE [secondid] = @secondid
If @@error = 0
COMMIT TRAN

ELSE
ROLLBACK


Incorrect syntax near the keyword 'WHERE'
0
 
cyberkiwiCommented:
BEGIN TRAN
select @secondid = secondid from tblOne where firstid = @firstid
DELETE FROM tblTwo where secondid = @secondid
DELETE FROM [tblOne] WHERE [firstid] = @firstid
If @@error = 0
      COMMIT TRAN
ELSE
      ROLLBACK
0
 
cyberkiwiCommented:
That is if you are always deleting just one record, I thought you wanted something more general like batch deletes.
0
 
alrightAuthor Commented:
Oh yes, always deleting just one record. Working well now, thank you sir
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now